5.24

Consider the relation, r, shown in Figure 5.22. Give the result of the following query:

SELECT building, room_number,time_slot_id,COUNT(*)
FROM r
GROUP BY ROLLUP(building,room_number,time_slot_id)

---

university=# SELECT building, room_number,time_slot_id,COUNT(*)
university-# FROM r
university-# GROUP BY ROLLUP(building,room_number,time_slot_id);
 building | room_number | time_slot_id | count 
----------+-------------+--------------+-------
          |             |              |     6
 Saucon   | 651         | A            |     1
 Garfield | 359         | B            |     1
 Painter  | 705         | D            |     1
 Saucon   | 550         | C            |     1
 Garfield | 359         | A            |     1
 Painter  | 403         | D            |     1
 Painter  | 705         |              |     1
 Saucon   | 550         |              |     1
 Saucon   | 651         |              |     1
 Painter  | 403         |              |     1
 Garfield | 359         |              |     2
 Saucon   |             |              |     2
 Garfield |             |              |     2
 Painter  |             |              |     2
(15 rows)

university=# 

But the output of given above is not much readable. The following is a bit better.

SELECT 
    (
        CASE 
            WHEN GROUPING(building) = 1 THEN '(all)'
            ELSE building
        END
    ) AS building, 
    (
        CASE 
            WHEN GROUPING(room_number) = 1 THEN '(all)'
            ELSE room_number
        END
    ) AS room_number, 
    (
        CASE 
            WHEN GROUPING(time_slot_id) = 1 THEN '(all)'
            ELSE time_slot_id
        END
    ) AS time_slot_id, 
    COUNT(*)
FROM r
GROUP BY ROLLUP(building,room_number,time_slot_id)
ORDER BY (building,room_number,time_slot_id) NULLS LAST;

OUTPUT:

 building | room_number | time_slot_id | count 
----------+-------------+--------------+-------
 Garfield | 359         | A            |     1
 Garfield | 359         | B            |     1
 Garfield | 359         | (all)        |     2
 Garfield | (all)       | (all)        |     2
 Painter  | 403         | D            |     1
 Painter  | 403         | (all)        |     1
 Painter  | 705         | D            |     1
 Painter  | 705         | (all)        |     1
 Painter  | (all)       | (all)        |     2
 Saucon   | 550         | C            |     1
 Saucon   | 550         | (all)        |     1
 Saucon   | 651         | A            |     1
 Saucon   | 651         | (all)        |     1
 Saucon   | (all)       | (all)        |     2
 (all)    | (all)       | (all)        |     6
(15 rows)

That is more like it!

Just in case you want to replicate the instance given at Figure 5.22 in your db.

CREATE TABLE r(
    building VARCHAR(15),
    room_number VARCHAR(7),
    time_slot_id VARCHAR(4),
    course_id VARCHAR(8),
    sec_id   VARCHAR(8),
    PRIMARY KEY (building,room_number,time_slot_id,course_id,sec_id)
);

INSERT INTO r VALUES 
    ('Garfield','359','A','BIO-101','1'),
    ('Garfield','359','B','BIO-101','2'),
    ('Saucon','651','A','CS-101','2'),
    ('Saucon','550','C','CS-319','1'),
    ('Painter','705','D','MU-199','1'),
    ('Painter','403','D','FIN-201','1');