CUBE: like an Excel pivot table but inside SQL

In SQL, GROUP BY CUBE is a powerful grouping mechanism used to generate subtotals and grand totals for a dataset across multiple dimensions. It essentially computes all possible combinations of groupings for the specified columns.

Here’s an example:

Let’s say you have a table called Sales with the following structure:

You want to compute subtotals for each combination of Region and Product, as well as the grand total for all sales.

Result:

1. GROUP BY CUBE (Region, Product) generates all combinations of Region and Product, including:

   • Individual values of Region and Product.

   • Subtotals for each Region (irrespective of Product).

   • Subtotals for each Product (irrespective of Region).

   • A grand total (ignoring both Region and Product).

2. NULL values in the output represent groupings where the column is not considered in that particular subtotal or total. For example:

   • Region = NULL and Product = Widget A is the subtotal for all regions for Widget A.

   • Region = NULL and Product = NULL is the grand total.

GROUP BY CUBE is especially useful for generating reports and summaries.