SQL CUBE

What is the SQL CUBE keyword?

Calculates every possible subtotal across a set of GROUP BY columns in a single query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CUBE: Supports: PostgreSQL, SQL Server, Oracle, IBM Db2, Snowflake, Redshift, BigQuery. Not supported: MySQL, SQLite.

SQL CUBE Full Explanation

CUBE is an extension of GROUP BY that automatically adds all combinations of aggregations (subtotals) for the specified columns, plus a grand total. For n columns, CUBE produces 2^n grouping sets, making it ideal for multidimensional analysis similar to pivot tables in spreadsheets or OLAP cubes in data warehouses.Internally the database rewrites CUBE into GROUPING SETS, generating separate aggregate steps for each grouping set and then unions the results. NULL is placed in columns that are not part of a particular subtotal so that all results fit the same output schema. Many dialects expose the GROUPING or GROUPING_ID function to distinguish real NULLs from subtotal NULLs.CUBE is powerful but can be expensive because the number of grouping sets grows exponentially with the number of columns. Use it on low-cardinality columns or pair it with filters to reduce the result set. If you only need hierarchic subtotals, use ROLLUP instead, which is cheaper.

SQL CUBE Syntax

SELECT column_list, aggregate_function(expression) AS alias
FROM   table_name
GROUP  BY CUBE (col1, col2 [, col3 ...]);

SQL CUBE Parameters

col1, col2, ... (Column or expression) - Dimensions over which to compute all subtotals

Example Queries Using SQL CUBE

-- Full cube on two dimensions
SELECT region,
       product,
       SUM(sales_amount) AS total_sales
FROM   orders
GROUP  BY CUBE (region, product)
ORDER  BY region, product;

-- Include a regular GROUP BY column plus a cube
SELECT fiscal_year,
       region,
       product,
       SUM(sales_amount) AS total_sales,
       GROUPING(region, product) AS grp_flag
FROM   orders
GROUP  BY fiscal_year, CUBE (region, product);

Expected Output Using SQL CUBE

The first query returns:1
Every region-product subtotal2
Region-wide totals (product is NULL)3
Product-wide totals (region is NULL)4
One grand total row (both region and product are NULL)

Use Cases with SQL CUBE

Building sales dashboards that need product, region, and overall totals
Feeding OLAP or BI tools that expect all dimensional combinations
Rapid prototyping of pivot tables without multiple UNION ALL queries
Ad-hoc data exploration where analysts want every subtotal in one pass

Common Mistakes with SQL CUBE

Forgetting that CUBE can explode row count on high-cardinality columns
Treating NULLs in subtotal rows as missing data rather than subtotal indicators
Assuming MySQL supports CUBE (it does not as of 8.2)
Omitting ORDER BY and getting disorganized results

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between CUBE and ROLLUP?

ROLLUP builds subtotals hierarchically from left to right, producing n + 1 grouping sets. CUBE generates every combination, producing 2^n grouping sets.

How many rows will CUBE return?

For n columns, CUBE returns the sum of row counts for each of the 2^n grouping sets plus one optional grand total. Actual rows depend on data cardinality.

Does CUBE impact query performance?

Yes. Because CUBE calculates many grouping sets, it can consume more CPU and memory. Use it on small dimension columns or aggregate pre-filtered data.

How do I identify subtotal rows produced by CUBE?

Use the GROUPING or GROUPING_ID function. A returned value of 1 (or bit flag) indicates that the column value is a subtotal, not a real NULL.