Database Reference
In-Depth Information
mysql>
SELECT subject,
->
COUNT(score) AS n,
->
SUM(score) AS total,
->
AVG(score) AS average,
->
MIN(score) AS lowest,
->
MAX(score) AS highest
->
FROM expt WHERE score IS NULL GROUP BY subject;
+---------+---+-------+---------+--------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+--------+---------+
| Jane | 0 | NULL | NULL | NULL | NULL |
| Marvin | 0 | NULL | NULL | NULL | NULL |
+---------+---+-------+---------+--------+---------+
For
COUNT()
, the number of scores per subject is zero and is reported that way. On the
other hand,
SUM()
,
AVG()
,
MIN()
, and
MAX()
return
NULL
when there are no values to
summarize. If you don't want an aggregate value of
NULL
to display as
NULL
, use
IF
NULL()
to map it appropriately:
mysql>
SELECT subject,
->
COUNT(score) AS n,
->
IFNULL(SUM(score),0) AS total,
->
IFNULL(AVG(score),0) AS average,
->
IFNULL(MIN(score),'Unknown') AS lowest,
->
IFNULL(MAX(score),'Unknown') AS highest
->
FROM expt WHERE score IS NULL GROUP BY subject;
+---------+---+-------+---------+---------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+---------+---------+
| Jane | 0 | 0 | 0.0000 | Unknown | Unknown |
| Marvin | 0 | 0 | 0.0000 | Unknown | Unknown |
+---------+---+-------+---------+---------+---------+
COUNT()
is somewhat different with regard to
NULL
values than the other aggregate
functions. Like other aggregate functions,
COUNT(
expr
)
counts only non-
NULL
values,
but
COUNT(*)
counts rows, no matter what they contain. You can see the difference
between the forms of
COUNT()
like this:
mysql>
SELECT COUNT(*), COUNT(score) FROM expt;
+----------+--------------+
| COUNT(*) | COUNT(score) |
+----------+--------------+
| 8 | 5 |
+----------+--------------+
This tells us that there are eight rows in the
expt
table but that only five of them have
the
score
value filled in. The different forms of
COUNT()
can be very useful for counting
missing values. Just take the difference:
mysql>
SELECT COUNT(*) - COUNT(score) AS missing FROM expt;
+---------+
| missing |
