Use IFNULL( ) to map their results appropriately:

     
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>
mysql> SELECT subject,
    -> COUNT(score) AS n,
    -> SUM(score) 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 |  NULL |  0.0000 | Unknown | Unknown |
| Marvin  | 0 |  NULL |  0.0000 | Unknown | Unknown |
+---------+---+-------+---------+---------+---------+
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.	The IFNULL() function returns a value based on whether a specified expression evaluates to NULL.
2.	IFNULL() tests its first argument and returns it if it's not NULL, or returns its second argument otherwise
3.	IF( ) and IFNULL( ) are useful for catching divide-by-zero operations
4.	IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the fun
5.	SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);
6.	The following expression returns expr2 if expr1 is NULL, and expr1 otherwise:

Use IFNULL( ) to map their results appropriately: : IFNULL « Function « SQL / MySQL

Related examples in the same category