Previous

SQL CUBE

CUBE is an extension of the GROUP BY clause that generates all possible combinations of groupings, including subtotals and a grand total. It is useful for multidimensional analysis in reports.

 

Syntax of SQL CUBE

SELECT column_name1, column_name2, aggregate_function(column_name)  
FROM table_name  
GROUP BY CUBE(column_name1, column_name2);
  • column_name1, column_name2: The columns used for grouping.
  • aggregate_function(): Functions like SUM(), COUNT(), AVG(), MAX(), or MIN().
  • CUBE(): Generates all possible combinations of groupings.

 

Key Features of SQL CUBE

  • Computes subtotals at all grouping levels.
  • Computes the grand total for all rows.
  • Generates more group combinations than ROLLUP().
  • Useful for multi-dimensional reports.

 

Example of SQL CUBE

Consider the following table:

Table: Sales

Category Product Sales Amount
Electronics Laptop 1000
Electronics Phone 700
Furniture Chair 500
Furniture Table 800

Query: Compute total sales for all category and product combinations

SELECT category, product, SUM(sales_amount) AS total_sales  
FROM sales  
GROUP BY CUBE(category, product);

Output:

categoryproducttotal_sales
ElectronicsLaptop1000
ElectronicsPhone700
ElectronicsNULL1700
FurnitureChair500
FurnitureTable800
FurnitureNULL1300
NULLLaptop1000
NULLPhone700
NULLChair500
NULLTable800
NULLNULL3000

Explanation

  1. Group by category and product: Computes total sales per product.
  2. Subtotal for each category: NULL in product means a subtotal for that category.
  3. Subtotal for each product across all categories: NULL in category means subtotal for that product.
  4. Grand total: NULL in both category and product represents the total sales for all categories and products.

 

SQL CUBE vs. SQL ROLLUP

FeatureCUBEROLLUP
Generates subtotals✅✅
Generates grand total✅✅
Generates all possible groupings✅❌
Best for multi-dimensional data✅❌
Best for hierarchical data❌✅

 

SQL CUBE with COUNT()

Example: Count the number of products in each category and overall

SELECT category, product, COUNT(*) AS product_count  
FROM sales  
GROUP BY CUBE(category, product);

 

Summary

  • CUBE generates all possible combinations of grouping sets.
  • The NULL values represent subtotal or total rows.
  • More comprehensive than ROLLUP() because it includes all group combinations.
  • Use cases: Multi-dimensional analysis, financial reports, and business intelligence reports.
Previous