Database Reference
In-Depth Information
following view implements the weekend versus weekday trip summary discussed in
Recipe 8.1
:
mysql>
CREATE VIEW trip_summary_view AS
->
SELECT
->
COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),1,NULL)) AS weekend_trips,
->
COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),NULL,1)) AS weekday_trips
->
FROM driver_log;
Selecting from this view is much easier than selecting directly from the underlying table:
mysql>
SELECT * FROM trip_summary_view;
+---------------+---------------+
| weekend_trips | weekday_trips |
+---------------+---------------+
| 4 | 6 |
+---------------+---------------+
8.3. Finding Values Associated with Minimum and
Maximum Values
Problem
You want to know the values for other columns in the row that contains a minimum or
maximum value.
Solution
Use two statements and a user-defined variable. Or a subquery. Or a join.
Discussion
MIN()
and
MAX()
find an endpoint of a range of values, but you may also be interested
in other values from the row in which the value occurs. For example, you can find the
largest state population like this:
mysql>
SELECT MAX(pop) FROM states;
+----------+
| MAX(pop) |
+----------+
| 35893799 |
+----------+
But that doesn't show you which state has this population. The obvious attempt at getting
that information looks like this:
mysql>
SELECT MAX(pop), name FROM states WHERE pop = MAX(pop);
ERROR 1111 (HY000): Invalid use of group function
