Database Reference
In-Depth Information
| tricia | phil | 2394482 |
+---------+---------+-----------+
When using aggregate functions to produce per-group summary values, watch out for
the following trap, which involves selecting nonsummary table columns not related to
the grouping columns. Suppose that you want to know the longest trip per driver in the
driver_log
table:
mysql>
SELECT name, MAX(miles) AS 'longest trip'
->
FROM driver_log GROUP BY name;
+-------+--------------+
| name | longest trip |
+-------+--------------+
| Ben | 152 |
| Henry | 300 |
| Suzi | 502 |
+-------+--------------+
But what if you also want to show the date on which each driver's longest trip occurred?
Can you just add
trav_date
to the output column list? Sorry, that doesn't work:
mysql>
SELECT name, trav_date, MAX(miles) AS 'longest trip'
->
FROM driver_log GROUP BY name;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-07-29 | 502 |
+-------+------------+--------------+
The query does produce a result, but if you compare it to the full table (shown here),
you'll see that although the dates for Ben and Henry are correct, the date for Suzi is not:
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
← Ben's longest trip
| 2 | Suzi | 2014-07-29 | 391 |
| 3 | Henry | 2014-07-29 | 300 |
← Henry's longest trip
| 4 | Henry | 2014-07-27 | 96 |
| 5 | Ben | 2014-07-29 | 131 |
| 6 | Henry | 2014-07-26 | 115 |
| 7 | Suzi | 2014-08-02 | 502 |
← Suzi's longest trip
| 8 | Henry | 2014-08-01 | 197 |
| 9 | Ben | 2014-08-02 | 79 |
| 10 | Henry | 2014-07-30 | 203 |
+--------+-------+------------+-------+
So what's going on? Why does the summary statement produce incorrect results? This
happens because when you include a
GROUP
BY
clause in a query, the only values that
you can meaningfully select are the grouping columns or summary values calculated
