Summaries and NULL Values
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,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt GROUP BY subject;
+---------+---+-------+---------+--------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+--------+---------+
| Jane | 2 | 97 | 48.5000 | 47 | 50 |
| Marvin | 3 | 150 | 50.0000 | 45 | 53 |
+---------+---+-------+---------+--------+---------+
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