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 grouping-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:
