GROUPING SETS
GROUPING SETS lets you compute several different groupings in one query.
In this page:
Syntax
SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY GROUPING SETS ((column1), (column2), ());GROUPING SETS
GROUP BY GROUPING SETS ((a), (b), ()) computes totals by a, by b and overall in a single pass.
CUBE (a, b) generates every combination of the listed columns and ROLLUP a hierarchy. Rows not belonging to a grouping have NULL in that column.
It replaces several UNION ALL queries.
Note:
GROUPING SETS replaces multiple GROUP BY queries joined with UNION ALL.
Example: GROUPING SETS
shop=# SELECT region, item, SUM(qty) AS total
shop-# FROM sales GROUP BY GROUPING SETS ((region), (item), ())
shop-# ORDER BY region, item;
region | item | total
--------+------+-------
N | | 8
S | | 13
| book | 4
| lamp | 2
| pen | 15
| | 21⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Writing separate queries for each grouping
- Not labelling NULL placeholder rows
- Using CUBE on many columns
Chapter Summary
- Several groupings in one query
- CUBE gives all combinations
- ROLLUP gives a hierarchy
- Missing groups show NULL
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: