GROUPING SETS

What are GROUPING SETS?

GROUPING SETS lets you define multiple groupings in one query. Instead of running separate GROUP BY queries and combining them with UNION ALL, you specify all the groupings together.

Syntax

SELECT col1, col2, aggregate_function(col3)
FROM table_name
GROUP BY GROUPING SETS (
  (col1, col2),
  (col1),
  ()
);

When to Use

• Generating reports with subtotals and grand totals
• Combining multiple GROUP BY perspectives in one query
• Replacing multiple UNION ALL queries for different aggregation levels

Key Points

1. Multiple Groupings — Each set in GROUPING SETS defines a separate grouping.
2. Empty Set () — Represents the grand total (no grouping).
3. NULLs — Columns not in the current grouping set appear as NULL.
4. GROUPING() — The GROUPING() function distinguishes between real NULLs and grouping-level NULLs.
5. Performance — More efficient than UNION ALL of separate queries because the table is scanned once.