11.3

Consider the takes relation. Write an SQL query that computes a cross-tab that has a column for each of the years 2017 and 2018, and a column for all, and one row for each course, as well as a row for all. Each cell in the table, should contain the number of students who took the corresponding course in the corresponding year, with column all containing the aggregate across all years, and row all containing the aggregate across all courses.

---

Several SQL implementations, such as Microsoft SQL Server, and Oracle, support a pivot clause that allows creation of cross-tabs.

Note that I have NOT tried the following query on any DBMS. Just following the syntax in the book. So not sure even if it works or not!

WITH t(course_id, year_2017, year_2018) AS ( 
    SELECT course_id, year_2017, year_2018
    FROM takes
    PIVOT (
        COUNT(id)
        FOR year IN (2017, 2018)
    )
)
SELECT t.course_id, title, year_2017, year_2018, (year_2017 + year_2018) AS 'all'
FROM t INNER JOIN course ON (t.course_id = course.course_id)

UNION

SELECT 'all', 'all', (SELECT COUNT(id) FROM takes WHERE year = 2017), 
    (SELECT COUNT(id) FROM takes WHERE year = 2018)

The following is implemented using postgresql server version 13.8:

WITH t(course_id, year, no_of_students) AS ( 
    SELECT course_id, year, COUNT(id)
    FROM takes
    GROUP BY course_id, year
), x(course_id, year_2017, year_2018) AS ( 
    SELECT 
        DISTINCT course_id, 
        (
            SELECT t2.no_of_students
            FROM t AS t2
            WHERE t2.course_id = t1.course_id AND year = 2017
        ) AS year_2017,
        (
            SELECT t3.no_of_students
            FROM t AS t3
            WHERE t3.course_id = t1.course_id AND year = 2018
        ) AS year_2018
    FROM t AS t1
), x2(course_id, title, year_2017, year_2018) AS ( 
    SELECT course_id, title, COALESCE(year_2017, 0) AS year_2017, COALESCE(year_2018, 0) AS year_2018
    FROM x NATURAL JOIN course
)
SELECT course_id, title, year_2017, year_2018, (year_2017 + year_2018) AS all
FROM x2

UNION

SELECT '(all)', '(all)', (SELECT SUM(year_2017) FROM x2), (SELECT SUM(year_2018) FROM x2),
(SELECT SUM(year_2017) FROM x2) + (SELECT SUM(year_2018) FROM x2);

Running the above query returns the following on my instance of the relation takes.

 course_id |           title            | year_2017 | year_2018 | all 
-----------+----------------------------+-----------+-----------+-----
 CS-347    | Database System Concepts   |         2 |         0 |   2
 FIN-201   | Investment Banking         |         0 |         1 |   1
 BIO-101   | Intro. to Biology          |         1 |         0 |   1
 MU-199    | Music Video Production     |         0 |         1 |   1
 BIO-301   | Genetics                   |         0 |         1 |   1
 EE-181    | Intro. to Digital Systems  |         1 |         0 |   1
 CS-101    | Intro. to Computer Science |         6 |         1 |   7
 CS-319    | Image Processing           |         0 |         2 |   2
 HIS-351   | World History              |         0 |         1 |   1
 (all)     | (all)                      |        13 |         9 |  22
 PHY-101   | Physical Principles        |         1 |         0 |   1
 CS-190    | Game Design                |         2 |         0 |   2
 CS-315    | Robotics                   |         0 |         2 |   2
(13 rows)