Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

CUBE for cross-tabulation

CUBE generates every possible combination of subtotals across a set of columns - all individual breakdowns, every mix of two or more, and the grand total - in a single query.

Example

Creating Retail Cross-Tab Totals with CUBE

SELECT category, region, channel, SUM(revenue) AS total_revenue
FROM store_sales
GROUP BY CUBE(category, region, channel)

Result:

Step by step

1. Run the query without SUM so you can see what the unfiltered values look like.

   SELECT category, region, channel, revenue
   FROM store_sales
   GROUP BY CUBE(category, region, channel);

2. SUM totals the numbers in a column. Works on numeric columns only.

   SELECT category, region, channel, SUM(revenue)
   FROM store_sales
   GROUP BY CUBE(category, region, channel);

3. AS gives the SUM result a friendly column name so the output reads cleanly.

   SELECT category, region, channel, SUM(revenue) AS total_revenue
   FROM store_sales
   GROUP BY CUBE(category, region, channel);

Practice

Try each one in your head first, then open the answer.

SELECT order_id, customer_id, COUNT(*) FROM orders GROUP BY CUBE(order_id, customer_id)

SELECT sale_id, store_id, COUNT(*) FROM store_sales GROUP BY CUBE(sale_id, store_id)

SELECT product_id, brand, COUNT(*) FROM products GROUP BY CUBE(product_id, brand)

SELECT review_id, product_id, COUNT(*) FROM reviews GROUP BY CUBE(review_id, product_id)

SELECT employee_id, full_name, COUNT(*) FROM employees GROUP BY CUBE(employee_id, full_name)