# PostgreSQL Tutorial: ROLLUP

August 3, 2023

Summary: in this tutorial, you will learn how to use the PostgreSQL `ROLLUP` to generate multiple grouping sets.

## Introduction to the PostgreSQL `ROLLUP`

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);
``````

## PostgreSQL `ROLLUP` examples

If you haven’t create the sales table, you can use the following script:

``````DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
brand VARCHAR NOT NULL,
segment VARCHAR NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
('ABC', 'Basic', 200),
('XYZ', 'Basic', 300);
``````

The following query uses the `ROLLUP` clause to find the number of products sold by brand (subtotal) and by all brands and segments (total).

``````SELECT
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
ROLLUP (brand, segment)
ORDER BY
brand,
segment;
``````

As you can see clearly from the output, the third row shows the sales of the `ABC` brand, the sixth row displays sales of the the `XYZ` brand. The last row shows the grand total for all brands and segments. In this example, the hierarchy is `brand > segment`.

If you change the order of brand and segment, the result will be different as follows:

``````SELECT
segment,
brand,
SUM (quantity)
FROM
sales
GROUP BY
ROLLUP (segment, brand)
ORDER BY
segment,
brand;
``````

In this case, the hierarchy is the `segment > brand`.

The following statement performs a partial roll-up:

``````SELECT
segment,
brand,
SUM (quantity)
FROM
sales
GROUP BY
segment,
ROLLUP (brand)
ORDER BY
segment,
brand;
``````

See the following `rental` table from the sample database.

The following statement finds the number of rental per day, month, and year by using the `ROLLUP`:

``````SELECT
EXTRACT (YEAR FROM rental_date) y,
EXTRACT (MONTH FROM rental_date) M,
EXTRACT (DAY FROM rental_date) d,
COUNT (rental_id)
FROM
rental
GROUP BY
ROLLUP (
EXTRACT (YEAR FROM rental_date),
EXTRACT (MONTH FROM rental_date),
EXTRACT (DAY FROM rental_date)
);
``````

In this tutorial, you have learned how to use the PostgreSQL `ROLLUP` to generate multiple grouping sets.