GROUPING SETS for multiple groupings in PostgreSQL - Step-by-Step Execution

Concept Flow - GROUPING SETS for multiple groupings

Start Query

↓

FROM table

↓

Apply GROUPING SETS

↓

Calculate aggregates for each grouping set

↓

Combine results

↓

Return final result set

The query reads data from the table, applies multiple grouping sets, calculates aggregates for each set, combines all results, and returns them.

Execution Sample

PostgreSQL

SELECT department, role, COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS ((department), (role));

This query counts employees grouped by department and separately by role using GROUPING SETS.

Execution Table

Step	Action	Grouping Set	Group Key Values	Aggregate Calculation	Output Row
1	Read row	-	department=Sales, role=Manager	-	-
2	Read row	-	department=Sales, role=Developer	-	-
3	Read row	-	department=HR, role=Manager	-	-
4	Apply grouping set 1	(department)	Sales	COUNT=2	(Sales, NULL, 2)
5	Apply grouping set 1	(department)	HR	COUNT=1	(HR, NULL, 1)
6	Apply grouping set 2	(role)	Manager	COUNT=2	(NULL, Manager, 2)
7	Apply grouping set 2	(role)	Developer	COUNT=1	(NULL, Developer, 1)
8	Combine results	-	-	-	4 rows total
9	Return result	-	-	-	Query ends

💡 All rows processed and aggregates computed for each grouping set.

Variable Tracker

Variable	Start	After Step 4	After Step 5	After Step 6	After Step 7	Final
department group counts	{}	{"Sales":2}	{"Sales":2,"HR":1}	{"Sales":2,"HR":1}	{"Sales":2,"HR":1}	{"Sales":2,"HR":1}
role group counts	{}	{}	{}	{"Manager":2}	{"Manager":2,"Developer":1}	{"Manager":2,"Developer":1}

Key Moments - 2 Insights

Why do we see NULL in the role column when grouping by department?

When grouping by department only, the role column is not part of the grouping key, so it shows NULL in those rows as seen in execution_table rows 4 and 5.

How does GROUPING SETS differ from using multiple GROUP BY queries?

GROUPING SETS runs all groupings in one query and combines results, as shown in execution_table steps 4-7, instead of running separate queries for each grouping.

Visual Quiz - 3 Questions

Look at the execution_table at step 6, what is the grouping set applied?

A (department)

B (department, role)

C (role)

D No grouping set applied

💡 Hint

Check the 'Grouping Set' column at step 6 in the execution_table.

At which step does the query combine all grouping set results?

A Step 8

B Step 7

C Step 4

D Step 9

💡 Hint

Look for the action 'Combine results' in the execution_table.

If we add (department, role) to GROUPING SETS, how would variable 'department group counts' change after step 7?

A It would remain the same

B It would include counts grouped by both department and role

C It would reset to empty

D It would only count roles

💡 Hint

Consider how adding a new grouping set affects counts tracked per grouping key.

Concept Snapshot

GROUPING SETS syntax:
SELECT columns, aggregate()
FROM table
GROUP BY GROUPING SETS ((group1), (group2), ...);

It runs multiple groupings in one query.
Each grouping set produces separate grouped results.
NULL appears in columns not in the current grouping set.

Full Transcript

This visual execution shows how a PostgreSQL query with GROUPING SETS works step-by-step. The query reads each row from the employees table. Then it applies each grouping set separately: first grouping by department, then by role. For each grouping set, it counts how many rows belong to each group. The results from all grouping sets are combined into one final result set. Variables track counts per group key. Nulls appear in columns not included in the current grouping set. This method is efficient to get multiple groupings in one query instead of running many queries.