Rollup

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

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

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

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

The syntax of GROUP BY () is equivalent to specifying a grand aggregate (as though there were no group by at all).

The result of the previous rollup 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, null as col2, count(*) FROM <table>;

Consider the following example:

SELECT state, city, COUNT(*) FROM citizens GROUP BY ROLLUP (state, 
city);

This example gives the following results:

A count for each state/city
A count for each state
A grand count