Cube

The cube syntax also gives aggregation results at multiple grouping levels in a single result set. For example, the following returns counts at four grouping levels:

SELECT col1, col2, COUNT(*) FROM <table> GROUP BY CUBE (col1, col2);

The group levels are returned as follows, with results as though the GROUP BYs were specified simultaneously:

GROUP BY col1, col2
GROUP BY col1
GROUP BY col2
GROUP BY ()

The result of the previous cube operation is equivalent to the following UNION result:

SELECT col1, col2, count(*) FROM <table> GROUP BY col1, col2 UNION all
SELECT col1, null AS col2, COUNT(*) FROM <table> GROUP BY col1 UNION 
all
SELECT null as col1, col2, count(*) FROM <table> GROUP BY col2 UNION 
all
SELECT null as col1, null AS col2, count(*) FROM <table>;

Consider the following example:

SELECT size, color, SUM(sales) FROM citizens GROUP BY CUBE (size, 
color);

This example gives the following results:

• Total sales for each size/color combination
• Total sales for each size
• Total sales for each color
• Grand total sales