Some aggregate functions can still produce NULL as a result.

       
mysql>
mysql>
mysql> CREATE TABLE expt
    -> (
    ->  subject VARCHAR(10),
    ->  test    VARCHAR(5),
    ->  score   INT
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','A',47);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','B',50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','C',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','D',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','A',52);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','B',45);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','C',53);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','D',NULL);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM expt;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane    | A    |    47 |
| Jane    | B    |    50 |
| Jane    | C    |  NULL |
| Jane    | D    |  NULL |
| Marvin  | A    |    52 |
| Marvin  | B    |    45 |
| Marvin  | C    |    53 |
| Marvin  | D    |  NULL |
+---------+------+-------+
8 rows in set (0.00 sec)

mysql>
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 |
+---------+---+-------+---------+--------+---------+
2 rows in set (0.00 sec)

mysql>
mysql> drop table expt;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	SUM() function returns the sum of the expression.
2.	SUM() the commission
3.	Sum by group
4.	What is the total amount of mail traffic and the average size of each message?
5.	What is the total population of the United States?
6.	Summaries and NULL Values
7.	To determine how many drivers were on the road and how many miles were driven each day
8.	Finding Smallest or Largest Summary Values
9.	The number of votes for each programming language is determined with SUM(IF(...))
10.	To compute the runner's average speed at the end of each stage

Some aggregate functions can still produce NULL as a result. : SUM « Aggregate Functions « SQL / MySQL

Related examples in the same category