Mega Code Archive
COUNT and GROUP BY
<![CDATA[
mysql>
mysql>
mysql> CREATE TABLE messages (
-> msgID int(20) NOT NULL auto_increment PRIMARY KEY,
-> forumID int(11),
-> rootID int(11),
-> parentID int(11),
-> userID int(11),
-> subject varchar(80),
-> msgText text,
-> ts timestamp
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO messages VALUES (60,1002,60,NULL,375,'cgi','','2001-03-27 08:35:28'),
-> (62,3,62,NULL,162,'Message','','2001-03-29 16:07:29');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT COUNT(*) AS answerCount, rootID FROM messages
-> GROUP BY rootID ORDER BY answerCount DESC LIMIT 5;
+-------------+--------+
| answerCount | rootID |
+-------------+--------+
| 1 | 60 |
| 1 | 62 |
+-------------+--------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table messages;
Query OK, 0 rows affected (0.00 sec)
]]>