Generating Summaries - MySQL - page 292

mysql> SELECT trav_date, COUNT(trav_date) FROM driver_log
-> GROUP BY trav_date HAVING COUNT(trav_date) = 1;
+------------+------------------+
| trav_date | COUNT(trav_date) |
+------------+------------------+
| 2014-07-26 | 1 |
| 2014-07-27 | 1 |
| 2014-08-01 | 1 |
+------------+------------------+
mysql> SELECT trav_date, COUNT(trav_date) FROM driver_log
-> GROUP BY trav_date HAVING COUNT(trav_date) > 1;
+------------+------------------+
| trav_date | COUNT(trav_date) |
+------------+------------------+
| 2014-07-29 | 3 |
| 2014-07-30 | 2 |
| 2014-08-02 | 2 |
+------------+------------------+
This technique works for combinations of values, too. For example, to find message
sender/recipient pairs between whom only one message was sent, look for combinations
that occur only once in the mail table:
mysql> SELECT srcuser, dstuser FROM mail
-> GROUP BY srcuser, dstuser HAVING COUNT(*) = 1;
+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| gene | tricia |
| phil | barb |
| tricia | gene |
| tricia | phil |
+---------+---------+
Note that this query doesn't print the count. The previous examples did so, to show that
the counts were being used properly, but you can refer to an aggregate value in a HAV
ING clause without including it in the output column list.
8.9. Grouping by Expression Results
Problem
You want to group rows into subgroups based on values calculated from an expression.
Solution
In the GROUP BY clause, use an expression that categorizes values.