Database Reference
In-Depth Information
from the groups. If you display additional table columns, they're not tied to the grouped
columns and the values displayed for them are indeterminate. (For the statement just
shown, it appears that MySQL may simply be picking the first date for each driver,
regardless of whether it matches the driver's maximum mileage value.)
To make queries that pick indeterminate values illegal so that you won't inadvertantly
suppose that the
trav_date
values are correct, set the
ONLY_FULL_GROUP_BY
SQL mode:
mysql>
SET sql_mode = 'ONLY_FULL_GROUP_BY';
mysql>
SELECT name, trav_date, MAX(miles) AS 'longest trip'
->
FROM driver_log GROUP BY name;
ERROR 1055 (42000): 'cookbook.driver_log.trav_date' isn't in GROUP BY
The general solution to the problem of displaying contents of rows associated with
minimum or maximum group values involves a join. The technique is described in
Recipe 14.7
. For the problem at hand, produce the required results as follows:
mysql>
CREATE TEMPORARY TABLE t
->
SELECT name, MAX(miles) AS miles FROM driver_log GROUP BY name;
mysql>
SELECT d.name, d.trav_date, d.miles AS 'longest trip'
->
FROM driver_log AS d INNER JOIN t USING (name, miles) ORDER BY name;
+-------+------------+--------------+
| name | trav_date | longest trip |
+-------+------------+--------------+
| Ben | 2014-07-30 | 152 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+--------------+
8.6. Summaries and NULL Values
Problem
You're summarizing a set of values that may include
NULL
values and you need to know
how to interpret the results.
Solution
Understand how aggregate functions handle
NULL
values.
Discussion
Most aggregate functions ignore
NULL
values.
COUNT()
is different:
COUNT(
expr
)
ignores
NULL
instances of
expr
, but
COUNT(*)
counts rows, regardless of content.
Suppose that an
expt
table contains experimental results for subjects who are to be given
four tests each and that lists the test score as
NULL
for tests not yet administered:
