Introduction to the PostgreSQL ROLLUP

The PostgreSQLROLLUP is a subclause of theGROUP BY clause that offers a shorthand for defining multiplegrouping sets. A grouping set is a set of columns by which you group. Check out thegrouping sets tutorial for detailed information.

Different from theCUBE subclause,ROLLUP does not generate all possible grouping sets based on the specified columns. It just makes a subset of those.

TheROLLUP assumes a hierarchy among the input columns and generates all grouping sets that make sense considering the hierarchy. This is the reason whyROLLUP is often used to generate the subtotals and the grand total for reports.

For example, theCUBE (c1,c2,c3) makes all eight possible grouping sets:

(c1, c2, c3)(c1, c2)(c2, c3)(c1,c3)(c1)(c2)(c3)()

However, theROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchyc1 > 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 hierarchyyear > month > date

The following illustrates the syntax of the PostgreSQLROLLUP:

SELECT    c1,    c2,    c3,    aggregate(c4)FROM    table_nameGROUP 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_nameGROUP BY    c1,    ROLLUP (c2, c3);

PostgreSQL ROLLUP examples

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

DROP TABLE IF EXISTS sales;CREATE TABLE sales (    brandVARCHAR NOT NULL,    segmentVARCHAR NOT NULL,    quantityINT NOT NULL,    PRIMARY KEY (brand, segment));INSERT INTO sales (brand, segment, quantity)VALUES    ('ABC','Premium',100),    ('ABC','Basic',200),    ('XYZ','Premium',100),    ('XYZ','Basic',300);

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

SELECT    brand,    segment,    SUM (quantity)FROM    salesGROUP BY    ROLLUP (brand,segment)ORDER BY    brand,    segment;

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

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

SELECT    segment,    brand,    SUM (quantity)FROM    salesGROUP BY    ROLLUP (segment, brand)ORDER BY    segment,    brand;

In this case, the hierarchy is thesegment > brand.

The following statement performs a partial roll-up:

SELECT    segment,    brand,    SUM (quantity)FROM    salesGROUP BY    segment,    ROLLUP (brand)ORDER BY    segment,    brand;

See the followingrental table from thesample database.

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

SELECT    EXTRACT (YEAR FROM rental_date) y,    EXTRACT (MONTH FROM rental_date) M,    EXTRACT (DAY FROM rental_date) d,    COUNT (rental_id)FROM    rentalGROUP 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 PostgreSQLROLLUP to generate multiple grouping sets.

Last updated onJuly 1, 2024

On this page

• Introduction to the PostgreSQL ROLLUP
• PostgreSQL ROLLUP examples