A grouping set specifies one combination of grouping keys for which SQL computes aggregates. Combining several sets produces a drill-down report at multiple detail levels in one query. section 4.4
Power-production totals at three levels
A readings report can combine system-and-year totals, year totals, and an overall total: SELECT year(read_on) AS year, system_id, count(*), round(sum(power) / 4 / 1000, 2) AS kWh FROM readings GROUP BY GROUPING SETS ((year, system_id), year, ()) ORDER BY year NULLS FIRST, system_id NULLS FIRST; The empty grouping set () creates the overall row. Keys omitted from a set appear as NULL, so year-only rows have NULL system_id. section 4.4
Related shorthand expresses common grouping-set patterns:
- ROLLUP(year, system_id) generates hierarchical sets: (year, system_id), (year), and (). section 4.4
- CUBE(year, system_id) generates combinations: (year, system_id), (year), (system_id), and (). section 4.4
Listing 4.12 Explicitly using GROUPING SETS
SELECT year(read_on) AS year,
system_id,
count(*),
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY GROUPING SETS ((year, system_id), year, ())
ORDER BY year NULLS FIRST, system_id NULLS FIRST;
Inspect the runnable SQL that produces the multi-level readings report. section 4.4
