Generating Summaries - MySQL - page 302

mysql> SELECT name, AVG(miles) AS driver_avg FROM driver_log
-> GROUP BY name WITH ROLLUP;
+-------+------------+
| name | driver_avg |
+-------+------------+
| Ben | 120.6667 |
| Henry | 182.2000 |
| Suzi | 446.5000 |
| NULL | 216.6000 |
+-------+------------+
In each case, the output row with NULL in the name column represents the overall sum
or average calculated over all drivers.
WITH ROLLUP produces multiple summary levels if you group by more than one column.
The following statement shows the number of mail messages sent between each pair of
users:
mysql> SELECT srcuser, dstuser, COUNT(*)
-> FROM mail GROUP BY srcuser, dstuser;
+---------+---------+----------+
| srcuser | dstuser | COUNT(*) |
+---------+---------+----------+
| barb | barb | 1 |
| barb | tricia | 2 |
| gene | barb | 2 |
| gene | gene | 3 |
| gene | tricia | 1 |
| phil | barb | 1 |
| phil | phil | 2 |
| phil | tricia | 2 |
| tricia | gene | 1 |
| tricia | phil | 1 |
+---------+---------+----------+
Adding WITH ROLLUP causes the output to include an intermediate count for each srcus
er value (these are the lines with NULL in the dstuser column), plus an overall count at
the end:
mysql> SELECT srcuser, dstuser, COUNT(*)
-> FROM mail GROUP BY srcuser, dstuser WITH ROLLUP;
+---------+---------+----------+
| srcuser | dstuser | COUNT(*) |
+---------+---------+----------+
| barb | barb | 1 |
| barb | tricia | 2 |
| barb | NULL | 3 |
| gene | barb | 2 |
| gene | gene | 3 |
| gene | tricia | 1 |
| gene | NULL | 6 |
| phil | barb | 1 |