Categories:

Query Syntax

GROUP BY CUBE

GROUP BY CUBE is an extension of the GROUP BY clause similar to GROUP BY ROLLUP. In addition to producing all the rows of a GROUP BY ROLLUP, GROUP BY CUBE adds all the “cross-tabulations” rows. Sub-total rows are rows that further aggregate whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.

A CUBE grouping is equivalent to a series of grouping sets and is essentially a shorter specification. The N elements of a CUBE specification correspond to 2^N GROUPING SETS.

See also:

GROUP BY GROUPING SETS

Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY CUBE ( groupCube [ , groupCube [ , ... ] ] )
[ ... ]

Where:

groupCube ::= { <column_alias> | <position> | <expr> }

column_alias

Column alias appearing in the query block’s SELECT list.

position

Position of an expression in the SELECT list.

expr

Any expression on tables in the current scope.

Usage Notes

• Snowflake allows up to 7 elements (equivalent to 128 grouping sets) in each cube.

Examples

Start by creating and loading a table with information about sales from a chain store that has branches in different cities and states/territories.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

Run a cube query:

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY CUBE (state, city)
 ;

Output:

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY CUBE (state, city)
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| PR    | SJ      |    192 |
| CA    | NULL    |     39 |
| FL    | NULL    |    144 |
| PR    | NULL    |    192 |
| NULL  | NULL    |    375 |
| NULL  | SF      |     13 |
| NULL  | SJ      |    218 |
| NULL  | Miami   |     48 |
| NULL  | Orlando |     96 |
+-------+---------+--------+