COUNT and GROUP BY : COUNT « Aggregate Functions « SQL / MySQL

1. SQL / MySQL
2. Aggregate Functions
3. COUNT

COUNT and GROUP BY

       
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)

   
    
    
    
    
    
    
  

Related examples in the same category