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:



CUBE sub-clause in PostgreSQL

PostgreSQL CUBE is a sub-clause of the GROUP BY clause which allows us to generate multiple grouping sets.
As we know grouping set is a set of columns to which you want to group, but we can not create multiple grouping set in a single statement (multiple statements may be the cause of slow query performance), CUBE sub-clause resolve these issues.

Syntax of the CUBE sub-clause:



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


The query is the same as GROUP BY except we added a new CUBE sub-clause after GROUP BY clause. The query statement generates all possible grouping sets based on the dimension columns specified in CUBE. If the number of columns specified in the CUBE is n, then you will get the result in 2n combinations.
Note: PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated.

We can understand the use of the CUBE 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
    CUBE (product_name,category)
ORDER BY product_name, category;


Result:

The following query performs a partial cube:


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

Result:



PostgreSQL: Function and its uses

The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL ...