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:

Press + to interact
-- 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 ...

Access this course and 1400+ top-rated courses and projects.