SQL Academy · Lesson

CUBE for All Combinations

Every grouping combination at once.

CUBE for All Combinations is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is CUBE?

The CUBE extension in SQL generates every possible combination of grouping from a list of columns. Where ROLLUP creates a hierarchy, CUBE produces a full cross-product of subtotals — including the grand total.

Think of it as answering: "Give me every subtotal you can compute from these columns."

Setting Up the Table

We will use a sales table that tracks revenue by year, region, and product category. This kind of multi-dimensional data is where CUBE shines.

CREATE TABLE sales (
  year     INT,
  region   VARCHAR(20),
  category VARCHAR(20),
  revenue  NUMERIC(10,2)
);

INSERT INTO sales VALUES
  (2023, 'North', 'Electronics', 12000),
  (2023, 'North', 'Clothing',     8000),
  (2023, 'South', 'Electronics',  9500),
  (2023, 'South', 'Clothing',     6000),
  (2024, 'North', 'Electronics', 14000),
  (2024, 'North', 'Clothing',     9500),
  (2024, 'South', 'Electronics', 11000),
  (2024, 'South', 'Clothing',     7200);

Your First CUBE Query

The syntax is straightforward: replace GROUP BY with GROUP BY CUBE(...) and list the columns inside. SQL will generate all combinations of those columns as grouping sets.

SELECT
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

How Many Groupings Does CUBE Produce?

For n columns, CUBE produces 2ⁿ grouping sets — one for every subset of the column list, including the empty set (the grand total).

With 3 columns (year, region, category), that is 2³ = 8 grouping sets: each column alone, each pair, all three together, and none at all.

-- 3 columns => 8 grouping sets:
-- (year, region, category)
-- (year, region)
-- (year, category)
-- (region, category)
-- (year)
-- (region)
-- (category)
-- () <- grand total
SELECT COUNT(*) AS row_count
FROM (
  SELECT year, region, category, SUM(revenue)
  FROM sales
  GROUP BY CUBE(year, region, category)
) sub;

NULLs Mark the Rolled-Up Dimension

Just like ROLLUP, CUBE uses NULL to signal that a column has been aggregated over. If region is NULL in a result row, that row summarises all regions for that combination of the remaining columns.

Use GROUPING(col) to distinguish a real NULL value from an aggregation marker.

SELECT
  GROUPING(year)     AS g_year,
  GROUPING(region)   AS g_region,
  GROUPING(category) AS g_category,
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY g_year, g_region, g_category;

Making NULLs Readable with COALESCE

To make the output more readable in reports, wrap each grouped column in COALESCE, replacing the aggregation NULL with a descriptive label like 'ALL'.

SELECT
  COALESCE(CAST(year AS VARCHAR), 'ALL YEARS')     AS year,
  COALESCE(region,   'ALL REGIONS')                AS region,
  COALESCE(category, 'ALL CATEGORIES')             AS category,
  SUM(revenue)                                     AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBE vs ROLLUP — Key Difference

ROLLUP(a, b, c) only creates subtotals along one hierarchy: (a,b,c), (a,b), (a), (). It respects left-to-right order.

CUBE(a, b, c) creates every subset — including cross-sections like (a,c) or (b) alone — that ROLLUP skips entirely. Use CUBE when you need full multi-dimensional analysis with no predetermined hierarchy.

-- ROLLUP: 4 grouping sets
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, region);

-- CUBE: 4 grouping sets for 2 columns (same count here)
-- but adds the (region) subtotal that ROLLUP omits
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, region);

Partial CUBE

You can mix regular GROUP BY columns with a CUBE sub-list. Columns listed outside the CUBE(...) are always present in every grouping set, while only the columns inside get the full combination treatment.

-- year is fixed; CUBE only over region and category
SELECT
  year,
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY year, CUBE(region, category)
ORDER BY year, region, category;

Filtering with HAVING on CUBE Results

HAVING works on CUBE output exactly as it does with regular GROUP BY. You can filter out grouping rows whose aggregate value does not meet a threshold — for instance, keeping only rows where total revenue exceeds a minimum.

SELECT
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category)
HAVING SUM(revenue) > 15000
ORDER BY total_revenue DESC;

Combining CUBE with Window Functions

You can wrap a CUBE query in a CTE and then apply window functions to rank or compare rows within the result. This is a powerful pattern for building executive-level dashboards.

WITH cube_result AS (
  SELECT
    COALESCE(region,   'ALL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY CUBE(region, category)
)
SELECT
  region,
  category,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM cube_result
ORDER BY revenue_rank;

When to Choose CUBE

CUBE is the right tool when:

You need every subtotal combination for an ad-hoc or OLAP-style report.
There is no natural hierarchy among your grouping columns.
You want to let analysts slice the data any way they choose.

Avoid CUBE when the column count is large — 4 columns already produces 16 grouping sets, and 5 produces 32. Prefer ROLLUP or explicit GROUPING SETS to keep result sets manageable.

Quick Check

How many distinct grouping sets does GROUP BY CUBE(a, b, c, d) produce?

Lesson Recap

In this lesson you learned how CUBE generates every possible combination of grouping sets from a column list, making it ideal for multi-dimensional reporting.

Key takeaways:

GROUP BY CUBE(a, b, c) produces 2ⁿ grouping sets.
NULL in a result column means that dimension was aggregated over; use GROUPING() to detect it.
COALESCE turns aggregation NULLs into meaningful labels.
A partial CUBE (e.g., GROUP BY year, CUBE(region, category)) fixes some columns and cubes only the rest.
Use CUBE for full ad-hoc analysis; prefer ROLLUP or explicit GROUPING SETS when the hierarchy is known or the column count is large.

Frequently asked questions

Is the “CUBE for All Combinations” lesson free?

Yes — the full text of “CUBE for All Combinations” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “CUBE for All Combinations”?

Every grouping combination at once. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CUBE for All Combinations” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy