Open In App

PostgreSQL – ROLLUP

Improve
Improve
Like Article
Like
Save
Share
Report

The PostgreSQL ROLLUP belongs to the GROUP BY clause that provides a short cut for defining multiple grouping sets. Multiple columns grouped together forms a group set.

Unlike the CUBE subclause, ROLLUP does not yield all possible grouping sets based on the specified columns. It just makes a subset of those. The ROLLUP presupposes a hierarchy between the input columns and yields all grouping sets that make sense only if the hierarchy is considered. That’s why ROLLUP is usually used to generate the subtotals and the grand total for reports.

Syntax:

SELECT
    column1,
    column2,
    column3,
    aggregate(column4)
FROM
    table_name
GROUP BY
    ROLLUP (column1, column2, column3);

To better understand the concept let’s create a new table and proceed to the examples.
To create a sample table use the below command:

CREATE TABLE geeksforgeeks_courses(
    course_name VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (course_name, segment)
);

Now insert some data into it using the below command:

INSERT INTO geeksforgeeks_courses(course_name, segment, quantity)
VALUES
    ('Data Structure in Python', 'Premium', 100),
    ('Algorithm Design in Python', 'Basic', 200),
    ('Data Structure in Java', 'Premium', 100),
    ('Algorithm Design in Java', 'Basic', 300);

Now that our table is set let’s look into examples.

Example 1:
The following query uses the ROLLUP subclause to find the number of products sold by course_name(subtotal) and by all course_name and segments (total) as follows:

SELECT
    course_name,
    segment,
    SUM (quantity)
FROM
    geeksforgeeks_courses
GROUP BY
    ROLLUP (course_name, segment)
ORDER BY
    course_name,
    segment;

Output:

Example 2:
The following statement performs a partial ROLL UP as follows:

SELECT
    segment,
    course_name,
    SUM (quantity)
FROM
    geeksforgeeks_courses
GROUP BY
    segment,
    ROLLUP (course_name)
ORDER BY
    segment,
    course_name;

Output:


Last Updated : 26 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads