mysql>
mysql> CREATE TABLE Classes
-> (
-> ClassID SMALLINT NOT NULL PRIMARY KEY,
-> Dept CHAR(4) NOT NULL,
-> Level ENUM('Upper', 'Lower') NOT NULL,
-> TotalStudents TINYINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO Classes VALUES
-> (1001, 'ANTH', 'Upper', 25),
-> (1002, 'ANTH', 'Upper', 25),
-> (1003, 'MATH', 'Upper', 18),
-> (1004, 'ANTH', 'Lower', 19),
-> (1005, 'ENGL', 'Upper', 28),
-> (1006, 'MATH', 'Lower', 23),
-> (1007, 'ENGL', 'Upper', 25),
-> (1008, 'MATH', 'Lower', 29),
-> (1009, 'ANTH', 'Upper', 25),
-> (1010, 'ANTH', 'Lower', 30),
-> (1011, 'ENGL', 'Lower', 26),
-> (1012, 'MATH', 'Lower', 22),
-> (1013, 'ANTH', 'Upper', 27),
-> (1014, 'ANTH', 'Upper', 21),
-> (1015, 'ENGL', 'Lower', 25),
-> (1016, 'ENGL', 'Upper', 32);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT Dept, Level, SUM(TotalStudents) AS Total
-> FROM Classes
-> GROUP BY Dept, Level WITH ROLLUP;
+------+-------+-------+
| Dept | Level | Total |
+------+-------+-------+
| ANTH | Upper | 123 |
| ANTH | Lower | 49 |
| ANTH | NULL | 172 |
| ENGL | Upper | 85 |
| ENGL | Lower | 51 |
| ENGL | NULL | 136 |
| MATH | Upper | 18 |
| MATH | Lower | 74 |
| MATH | NULL | 92 |
| NULL | NULL | 400 |
+------+-------+-------+
10 rows in set (0.00 sec)
mysql>
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)