More GROUP BY Options: ROLLUP, CUBE, GROUPING SETS, and GROUPING()

| DQL

So far we’ve seen how GROUP BY can summarize data into meaningful results. But what if you need totals and subtotals in the same query—or even want to control exactly which combinations of groupings are returned? That’s where additional GROUP BY options come in: ROLLUP, CUBE, GROUPING SETS, and the GROUPING() function.

Let’s continue using the Gallery.Paintings example table from the previous post to demonstrate how these options can streamline reporting and analysis requests.

Creating Hierarchical Summaries with ROLLUP

Basic ROLLUP Syntax

SQL
SELECT column1,
	   column2,
	   AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY ROLLUP (column1, column2);

ROLLUP creates subtotals moving from right to left through the grouping list, plus a grand total.

Example: Paintings by Country and Artist, with Totals

SQL
SELECT CountryOfOrigin,
	   Artist,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy','Netherlands','Spain')
GROUP BY ROLLUP (CountryOfOrigin, Artist)
ORDER BY CountryOfOrigin,
         Artist;

Result:

CountryOfOriginArtistPaintingCount
ItalyLeonardo da Vinci2
ItalyNULL2
NetherlandsJohannes Vermeer3
NetherlandsVincent van Gogh3
NetherlandsNULL6
SpainSalvador Dalí3
SpainNULL3
NULLNULL11

Exploring All Combinations with CUBE

Basic CUBE Syntax

SQL
SELECT column1,
	   column2,
	   AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY CUBE (column1, column2);

CUBE generates subtotals for all possible combinations of the grouping columns, plus a grand total.

Example: Paintings by Country and Artist, with All Combinations

SQL
SELECT CountryOfOrigin,
	   Artist,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy','Netherlands','Spain')
GROUP BY CUBE (CountryOfOrigin, Artist)
ORDER BY CountryOfOrigin,
         Artist;

Result:

CountryOfOriginArtistPaintingCount
ItalyLeonardo da Vinci2
ItalyNULL2
NetherlandsJohannes Vermeer3
NetherlandsVincent van Gogh3
NetherlandsNULL6
SpainSalvador Dalí3
SpainNULL3
NULLLeonardo da Vinci2
NULLJohannes Vermeer3
NULLVincent van Gogh3
NULLSalvador Dalí3
NULLNULL11

Customizing Subtotals with GROUPING SETS

Basic GROUPING SETS Syntax

SQL
SELECT column1,
	   column2,
	   AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY GROUPING SETS (
	(column1, column2),  -- detail
	(column1),           -- subtotal by column1
	(column2),           -- subtotal by column2
	()                   -- grand total
);

GROUPING SETS lets you explicitly define which detail levels, subtotals, and totals to include.

Example: Paintings by Country and Century, with Custom Subtotals

SQL
SELECT CountryOfOrigin,
	   (YearCreated / 100) * 100 AS Century,
	   COUNT(1) AS PaintingCount
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy','Netherlands','Spain')
GROUP BY GROUPING SETS (
	(CountryOfOrigin, (YearCreated / 100) * 100), -- detail
	(CountryOfOrigin),                            -- subtotal by country
	((YearCreated / 100) * 100),                  -- subtotal by century
	()                                            -- grand total
)
ORDER BY CountryOfOrigin,
		 Century;

Result:

CountryOfOriginCenturyPaintingCount
Italy14001
Italy15001
ItalyNULL2
Netherlands16003
Netherlands18003
NetherlandsNULL6
Spain19003
SpainNULL3
NULL14001
NULL15001
NULL16003
NULL18003
NULL19003
NULLNULL11

Distinguishing Totals with GROUPING()

Basic GROUPING() Syntax

SQL
SELECT column1,
	   column2,
	   AGGREGATE_FUNCTION(column3),
	   GROUPING(column1) AS IsCol1Subtotal,
	   GROUPING(column2) AS IsCol2Subtotal
FROM table_name
GROUP BY ROLLUP (column1, column2);

GROUPING() helps you distinguish between:

  • Real NULL values in your data
  • NULLs introduced by ROLLUP, CUBE, or GROUPING SETS as subtotal markers

Table: Gallery.Statues

StatueIDTitleArtistYearCreatedCountryOfOrigin
1Venus di MiloNULL-125Greece
2DavidMichelangelo di Lodovico Buonarroti Simoni1504Italy
3The ThinkerAuguste Rodin1904France

Example: Using GROUPING() with Statues by Country and Artist

SQL
SELECT CountryOfOrigin,
	   Artist,
	   COUNT(1) AS StatueCount,
	   GROUPING(CountryOfOrigin) AS IsCountrySubtotal,
	   GROUPING(Artist) AS IsArtistSubtotal
FROM Gallery.Statues
GROUP BY ROLLUP (CountryOfOrigin, Artist)
ORDER BY CountryOfOrigin,
		 Artist;

Result:

CountryOfOriginArtistStatueCountIsCountrySubtotalIsArtistSubtotal
FranceAuguste Rodin100
FranceNULL (subtotal)101
GreeceNULL100
GreeceNULL (subtotal)101
ItalyMichelangelo di Lodovico Buonarroti Simoni100
ItalyNULL (subtotal)101
NULLNULL311

Which Option to Choose

  • ROLLUP → Best for hierarchies (e.g., Country → Artist → Grand Total). Use it when you need running subtotals and a grand total.
  • CUBE → Best for multi-dimensional analysis where you want all possible combinations of grouping columns (like pivot table summaries).
  • GROUPING SETS → Best for precise control, when you want only specific subtotals and totals without the “extra” combinations.
  • GROUPING() → Best for clarity in reporting, to tell the difference between a true NULL in your data and a subtotal row introduced by grouping logic.

Compatibility and Syntax Differences

  • SQL Server → Supports ROLLUP, CUBE, GROUPING SETS, and GROUPING().
  • Oracle → Supports all, but may require EXTRACT() for date parts.
  • PostgreSQL → Full support for all features.
  • MySQL → Supports ROLLUP (via WITH ROLLUP), but not CUBE, GROUPING SETS, or GROUPING().
  • SQLite → Does not support these features directly; you can simulate with UNION ALL.

Key Takeaways

  • ROLLUP adds hierarchical subtotals and a grand total.
  • CUBE generates all possible subtotal and total combinations.
  • GROUPING SETS lets you define exactly which subtotals to include.
  • GROUPING() distinguishes between actual NULL values and those added by grouping.
  • Feature support varies across platforms—SQL Server, Oracle, and PostgreSQL have full support, while MySQL and SQLite are more limited.

As you’ve seen, advanced grouping options often produce subtotal and total rows with NULL placeholders. In the next post, we’ll look at CASE expressions which can be combined with GROUPING() to replace those NULLs with clear, meaningful labels.

Related Posts

Scroll to Top