Skip to main content

ROLLUP sub-clause in PostgreSQL


PostgreSQL ROLLUP is a sub-clause of the GROUP BY clause that generates a result set which shows the aggregate for the hierarchy of values in the selected columns. It allows multiple groupings set the same as CUBE sub-clause, but there is only one difference between CUBE and ROLLUP.
CUBE sub-clause generates a result set that shows aggregate for all combinations of values in the selected columns
ROLLUP is a sub-clause of the GROUP BY clause that generates a result set which shows the aggregate for the hierarchy of values in the selected columns.

CUBE(Col_1, Col_2, Col_3)
---------------------------------
Col_1, Col_2, Col_3
Col_1, Col_2
Col_1, Col_3
Col_1
Col_2,Col_3
Col_2
Col_3
()



ROLLUP(Col_1, Col_2, Col_3)
---------------------------------
Col_1, Col_2, Col_3
Col_1, Col_2
Col_1
()


Syntax of the ROLLUP sub-clause:


SELECT
    c1,
    c2,
    c3,
    aggregate (c4)
FROM
    table_name
GROUP BY
    ROLLUP (c1, c2, c3);


Query is same as GROUP BY except  added a new ROLLUP sub-clause after GROUP BY clause.
We can understand the use of ROLLUP sub-clause by example.
Suppose we have a PRODUCTS table with columns like product name, category, quantity, etc.

CREATE TABLE products
(
                product_id serial NOT NULL,
                product_name text,
                category text,
                quantity numeric
);
-- Inserted some records into PRODUCTS table

INSERT INTO products(product_name, category, quantity)
VALUES('prod_1','category_1',100);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_2','category_2',200);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_3','category_3',400);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_4','category_4',500);

CUBE sub-clause by example


SELECT
    product_name,
    category,
                SUM(quantity)
FROM
    products
GROUP BY
    ROLLUP (product_name,category)
ORDER BY product_name, category;

Result: