SQL CUBE

Summary: in this tutorial, you will learn how to use the SQL CUBE to generate subtotals for the output of a query.

Introduction to SQL CUBE

Similar to the ROLLUP, CUBE is an extension of the GROUP BY clause. CUBE allows you to generate subtotals like the ROLLUP extension. In addition, the CUBE extension will generate subtotals for all combinations of grouping columns specified in the GROUP BY clause.

The following illustrates the syntax of CUBE extension:

SELECT 
    c1, c2, AGGREGATE_FUNCTION(c3)
FROM
    table_name
GROUP BY CUBE(c1 , c2);

In this syntax, we have two columns specified in the CUBE. The statement creates two subtotal combinations. Generally, if you have n number of columns listed in the CUBE, the statement will create 2n subtotal combinations.

SQL CUBE examples

We will reuse the inventory table created in the ROLLUP tutorial.

SQL CUBE with one column example

The following statement uses the SUM() function and the GROUP BY clause to find the total inventory of every warehouse:

SELECT
   warehouse,
   SUM(quantity)
FROM
   inventory
GROUP BY
   warehouse;

If you want to know the total inventory in all warehouses, you use the CUBE extension in the GROUP BY clause as follows:

SELECT
   warehouse,
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse)
ORDER BY
   warehouse;  

In this example, the CUBE the extension adds a total inventory row with a null value in the warehouse column. The effect is the same as the ROLLUP function. To make the output more readable, you can use the COALESCE() function as shown below:

SELECT
   COALESCE(warehouse,'All warehouses'),
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse)
ORDER BY
   warehouse;

SQL CUBE with multiple columns example

The following statement finds the total inventory by warehouse and product:

SELECT
   warehouse,
   product,
   SUM(quantity)
FROM
   inventory
GROUP BY
   warehouse,product
ORDER BY
   warehouse,
   product;

When you use the CUBE function, the query makes four subtotals:

SELECT
   warehouse,
   product,
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse,product)
ORDER BY
   warehouse,
   product

As you can see in the output, we have four subtotal rows:

• The third and sixth rows show the total inventory of all products in the San Francisco and San Jose warehouses. The values in the product the column is null.
• The seventh and eighth rows display the total inventory by-products which are Samsung and iPhone in all warehouses. Hence, the values in the warehouse columns are null.

The last column is the grand total that shows the total inventory in all warehouses.

The following statement uses the COALESCE() function to substitute null values with more meaningful data:

SELECT
   COALESCE(warehouse, '...All Warehouses') warehouse,
  COALESCE(product, '...All Products') product,
   SUM(quantity) 
FROM
   inventory
GROUP BY
   CUBE(warehouse,product)
ORDER BY
   warehouse,
   product; 

Creating cross-tabular reports

The following query creates a cross-tabular report by retrieving data from the  employees the table in the sample database using the CUBE extension:

SELECT
   COALESCE(department_name, '-') department,
   COALESCE(job_title,'-') job,
   COUNT(*) ,
   SUM(salary) salary
FROM
   employees
INNER JOIN departments USING (department_id)
INNER JOIN jobs USING (job_id)
GROUP BY
   CUBE(department_name,job_title)
ORDER BY
   department_name ASC NULLS LAST;

Here is the result:

Department	Job	COUNT(*)	SALARY
Accounting	Accounting Manager	1	12000
Accounting	Public Accountant	1	8300
Accounting	–	2	20300
Administration	Administration Assistant	1	4400
Administration	–	1	4400
Executive	Administration Vice President	2	34000
Executive	President	1	24000
Executive	–	3	58000
Finance	Accountant	5	39600
Finance	Finance Manager	1	12000
Finance	–	6	51600
Human Resources	Human Resources Representative	1	6500
Human Resources	–	1	6500
IT	Programmer	5	28800
IT	–	5	28800
Marketing	Marketing Manager	1	13000
Marketing	Marketing Representative	1	6000
Marketing	–	2	19000
Public Relations	Public Relations Representative	1	10000
Public Relations	–	1	10000
Purchasing	Purchasing Clerk	5	13900
Purchasing	Purchasing Manager	1	11000
Purchasing	–	6	24900
Sales	Sales Manager	2	27500
Sales	Sales Representative	3	24000
Sales	–	5	51500
Shipping	Shipping Clerk	2	7900
Shipping	Stock Clerk	1	2700
Shipping	Stock Manager	4	30600
Shipping	–	7	41200
–	Accountant	5	39600
–	Accounting Manager	1	12000
–	Administration Assistant	1	4400
–	Administration Vice President	2	34000
–	Finance Manager	1	12000
–	Human Resources Representative	1	6500
–	Marketing Manager	1	13000
–	Marketing Representative	1	6000
–	President	1	24000
–	Programmer	5	28800
–	Public Accountant	1	8300
–	Public Relations Representative	1	10000
–	Purchasing Clerk	5	13900
–	Purchasing Manager	1	11000
–	Sales Manager	2	27500
–	Sales Representative	3	24000
–	Shipping Clerk	2	7900
–	Stock Clerk	1	2700
–	Stock Manager	4	30600
–	–	39	316200

In this tutorial, you have learned how to use the SQL CUBE extensions to generate the subtotal in the output of a query.