IntermediateGROUP BY & Aggregation

CUBE: all combinations of groupings

The query

SQL

SELECT
  COALESCE(country, 'ALL') AS country,
  COALESCE(tier, 'ALL') AS tier,
  COUNT(*) AS customer_count
FROM customers
GROUP BY CUBE(country, tier)
ORDER BY country NULLS LAST, tier NULLS LAST;

Tested against PostgreSQL 16

Note

“CUBE generates subtotals for ALL combinations of grouping columns. 2 columns = 4 subtotal rows.

Tables referenced

customers6 cols