rollup clause in SQL

The PostgreSQL ROLLUP is a subclause of the GROUP BY clause that offers a shorthand for defining multiple grouping sets. A grouping set is a set of columns by which you group. Check out the grouping sets tutorial for the detailed information.
Different from the CUBE subclause, ROLLUP does not generate all possible grouping sets based on the specified columns. It just makes a subset of those.
The ROLLUP assumes a hierarchy among the input columns and generates all grouping sets that make sense considering the hierarchy. This is the reason why ROLLUP is often used to generate the subtotals and the grand total for reports.
For example, the CUBE (c1,c2,c3) makes all eight possible grouping sets:
```
(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()
```
However, the ROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchy c1 > c2 > c3 as follows:
```
(c1, c2, c3)
(c1, c2)
(c1)
()
```
A common use of  ROLLUP is to calculate the aggregations of data by year, month, and date, considering the hierarchy year > month > date
The following illustrates the syntax of the PostgreSQL ROLLUP:
```
SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    ROLLUP (c1, c2, c3);
```
It is also possible to do a partial roll up to reduce the number of subtotals generated.
```
SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    c1, 
    ROLLUP (c2, c3);
```
Home