Generating Summaries - MySQL - page 284

Discussion
The summary statements shown so far calculate summary values over all rows in the
result set. For example, the following statement determines the number of records in
the mail table, and thus the total number of mail messages sent:
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
| 16 |
+----------+
To arrange a set of rows into subgroups and summarize each group, use aggregate func‐
tions in conjunction with a GROUP BY clause. To determine the number of messages per
sender, group the rows by sender name, count how many times each name occurs, and
display the names with the counts:
mysql> SELECT srcuser, COUNT(*) FROM mail GROUP BY srcuser;
+---------+----------+
| srcuser | COUNT(*) |
+---------+----------+
| barb | 3 |
| gene | 6 |
| phil | 5 |
| tricia | 2 |
+---------+----------+
That query summarizes the same column that is used for grouping ( srcuser ), but that's
not always necessary. Suppose that you want a quick characterization of the mail table,
showing for each sender listed in it the total amount of traffic sent (in bytes) and the
average number of bytes per message. In this case, you still use the srcuser column to
group the rows, but summarize the size values:
mysql> SELECT srcuser,
-> SUM(size) AS 'total bytes',
-> AVG(size) AS 'bytes per message'
-> FROM mail GROUP BY srcuser;
+---------+-------------+-------------------+
| srcuser | total bytes | bytes per message |
+---------+-------------+-------------------+
| barb | 156696 | 52232.0000 |
| gene | 1033108 | 172184.6667 |
| phil | 18974 | 3794.8000 |
| tricia | 2589407 | 1294703.5000 |
+---------+-------------+-------------------+
Use as many grouping columns as necessary to achieve a grouping as fine-grained as
you require. The earlier query that shows the number of messages per sender is a coarse
summary. To be more specific and find out how many messages each sender sent from