Database Reference
In-Depth Information
mysql>
SELECT
->
COUNT(IF(DAYOFWEEK(trav_date)=7,1,NULL)) AS 'Saturday trips',
->
COUNT(IF(DAYOFWEEK(trav_date)=1,1,NULL)) AS 'Sunday trips'
->
FROM driver_log;
+----------------+--------------+
| Saturday trips | Sunday trips |
+----------------+--------------+
| 3 | 1 |
+----------------+--------------+
Or to count weekend versus weekday trips, do this:
mysql>
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;
+---------------+---------------+
| weekend trips | weekday trips |
+---------------+---------------+
| 4 | 6 |
+---------------+---------------+
The
IF()
expressions determine, for each column value, whether it should be counted.
If so, the expression evaluates to
1
and
COUNT()
counts it. If not, the expression evaluates
to
NULL
and
COUNT()
ignores it. The effect is to count the number of values that satisfy
the condition given as the first argument to
IF()
.
Summarizing with MIN() and MAX()
Finding smallest or largest values in a dataset is somewhat akin to sorting, except that
instead of producing an entire set of sorted values, you select only a single value at one
end or the other of the sorted range. This operation applies to questions about smallest,
largest, oldest, newest, most expensive, least expensive, and so forth. One way to find
such values is to use the
MIN()
and
MAX()
functions. (Another way is to use
LIMIT
; see
Recipe 3.9
.)
Because
MIN()
and
MAX()
determine the extreme values in a set, they're useful for char‐
acterizing ranges:
• What date range is represented by the rows in the
mail
table? What are the smallest
and largest messages sent?
mysql>
SELECT
->
MIN(t) AS earliest, MAX(t) AS latest,
->
MIN(size) AS smallest, MAX(size) AS largest
->
FROM mail;
+---------------------+---------------------+----------+---------+
| earliest | latest | smallest | largest |
+---------------------+---------------------+----------+---------+
| 2014-05-11 10:15:08 | 2014-05-19 22:21:51 | 271 | 2394482 |
+---------------------+---------------------+----------+---------+
