cube clause in SQL

PostgreSQL CUBE is a subclause of the GROUP BY clause. The CUBE allows you to generate multiple grouping sets.
A grouping set is a set of columns to which you want to group. 
The following illustrates the syntax of the CUBE subclause:
```
SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    CUBE (c1, c2, c3);
```
In this syntax:
*    First, specify the CUBE subclause in the the GROUP BY clause of the SELECT statement.
*    Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze and aggregation function expressions.
*    Third, in the GROUP BY clause, specify the dimension columns within the parentheses of the CUBE subclause.
The query generates all possible grouping sets based on the dimension columns specified in CUBE. The CUBE subclause is a short way to define multiple grouping sets so the following are equivalent:
```
CUBE(c1,c2,c3) 

GROUPING SETS (
    (c1,c2,c3), 
    (c1,c2),
    (c1,c3),
    (c2,c3),
    (c1),
    (c2),
    (c3), 
    ()
 ) 
```
In general, if the number of columns specified in the CUBE is n, then you will have 2n combinations.
PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. The following shows the syntax:
```
SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    c1,
    CUBE (c1, c2);
```
Home