Mastering Advanced SQL Techniques with MySQL/

...

/

The GROUPING() Function

The GROUPING() Function

Expand your knowledge on GROUP BY WITH ROLLUP with the GROUPING modifier.

We'll cover the following...

MySQL provides a WITH ROLLUP modifier for a GROUP BY clause in a SELECT statement. The modifier that functions as a suffix to the GROUP BY’s list of expressions yields aggregate rows for the groups created through GROUP BY. This can be very helpful in scenarios where we would otherwise need to formulate a second query to retrieve the same information. A great example of this is the summary of car parts built by type and date:

MySQL
-- Inspect the table that records sample car model parts.
DESCRIBE CarPart;
-- Insert empty lines in the output for better readability.
SELECT "";
-- Summarize the car parts built by type and date.
SELECT name, built_at, COUNT(built_at) AS `Parts Built`
FROM CarPart
WHERE built_at IS NOT NULL
GROUP BY name, built_at
WITH ROLLUP;

Noticeably, we exclude rows where built_at is NULL with the corresponding WHERE clause (line 10). That is, we do not consider car parts that have not been built, yet. While this makes sense, the information on car parts that have not been built is valuable, nonetheless:

MySQL
-- Summarize the car parts built by type and date, including ones that still have to be built.
SELECT name, built_at, COUNT(built_at) AS `Parts Built`
FROM CarPart
GROUP BY name, built_at
WITH ROLLUP;

However, including this information in the statement’s output leaves us with a problem:

MySQL
-- Output of the previous SQL snippet.
+--------+----------+-----------+
|name    |built_at  |Parts Built|
+--------+----------+-----------+
|exterior|NULL      |0          | <-- Grouped row or aggregation?
|exterior|2022-03-24|1          |
|exterior|2022-03-26|2          |
|exterior|2022-03-27|2          |
|exterior|2022-03-28|3          |
|exterior|NULL      |8          | <-- Grouped row or aggregation?
|interior|NULL      |0          | <-- Grouped row or aggregation?
|interior|2022-03-26|1          |
|interior|2022-03-27|1          |
|interior|2022-03-28|3          |
|interior|NULL      |5          | <-- Grouped row or aggregation?
|wheels  |NULL      |0          | <-- Grouped row or aggregation?
|wheels  |2022-03-28|6          |
|wheels  |NULL      |6          | <-- Grouped row or aggregation?
|NULL    |NULL      |19         |
+--------+----------+-----------+

How do we know ...