Add the WITH ROLLUP option to the GROUP BY clause : ROLLUP « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. ROLLUP

Add the WITH ROLLUP option to the GROUP BY clause

     
mysql>
mysql>
mysql> CREATE TABLE CDs
    -> (
    ->     CDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     OnOrder SMALLINT UNSIGNED NOT NULL,
    ->     Reserved SMALLINT UNSIGNED NOT NULL,
    ->     Department ENUM('Classical', 'Popular') NOT NULL,
    ->     Category VARCHAR(20) NOT NULL,
    ->     RowUpdate TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO CDs (CDName, InStock, OnOrder, Reserved, Department, Category) VALUES
    -> ('Xml', 10, 5, 3, 'Popular', 'Rock'),
    -> ('Java', 10, 5, 3, 'Classical', 'Opera'),
    -> ('SQL', 17, 4, 1, 'Popular', 'Jazz'),
    -> ('MySQL', 9, 4, 2, 'Classical', 'Dance'),
    -> ('CSS', 24, 2, 5, 'Classical', 'General'),
    -> ('HTML', 16, 6, 8, 'Classical', 'Vocal'),
    -> ('Oracle', 2, 25, 6, 'Popular', 'Blues'),
    -> ('Javascript', 32, 3, 10, 'Popular', 'Jazz'),
    -> ('Data type', 12, 15, 13, 'Popular', 'Country'),
    -> ('Flash', 5, 20, 10, 'Popular', 'New Age'),
    -> ('Ajax', 24, 11, 14, 'Popular', 'New Age'),
    -> ('Photoshop', 42, 17, 17, 'Classical', 'General'),
    -> ('Word', 25, 44, 28, 'Classical', 'Dance'),
    -> ('iPhone', 32, 15, 12, 'Classical', 'General'),
    -> ('MacBook', 20, 10, 5, 'Classical', 'Opera'),
    -> ('Linux', 23, 12, 8, 'Classical', 'General'),
    -> ('Shell', 23, 10, 17, 'Popular', 'Country'),
    -> ('Pascal', 18, 20, 10, 'Popular', 'Jazz'),
    -> ('Ruby', 22, 5, 7, 'Popular', 'Blues'),
    -> ('Sql Server', 28, 17, 16, 'Classical', 'General'),
    -> ('Opera', 10, 35, 12, 'Classical', 'Opera'),
    -> ('Safari', 15, 30, 14, 'Popular', 'Blues'),
    -> ('C', 42, 0, 8, 'Popular', 'Blues'),
    -> ('C++', 16, 8, 8, 'Classical', 'General');
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT Department, Category, COUNT(*) AS Total
    -> FROM CDs
    -> GROUP BY Department, Category WITH ROLLUP;
+------------+----------+-------+
| Department | Category | Total |
+------------+----------+-------+
| Classical  | Dance    |     2 |
| Classical  | General  |     6 |
| Classical  | Opera    |     3 |
| Classical  | Vocal    |     1 |
| Classical  | NULL     |    12 |
| Popular    | Blues    |     4 |
| Popular    | Country  |     2 |
| Popular    | Jazz     |     3 |
| Popular    | New Age  |     2 |
| Popular    | Rock     |     1 |
| Popular    | NULL     |    12 |
| NULL       | NULL     |    24 |
+------------+----------+-------+
12 rows in set (0.00 sec)

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

   
    
    
    
    
  

Related examples in the same category