PostgreSQL - ROLLUP

The PostgreSQL ROLLUP clause is a powerful extension to the GROUP BY clause, providing a shortcut for defining multiple grouping sets. When multiple columns are grouped together, they form a grouping set. By organizing and aggregating data hierarchically, ROLLUP creates meaningful summaries without the need for complex subqueries. ROLLUP is particularly useful for generating subtotals and grand totals in reports.

In this article, we will explain the PostgreSQL ROLLUP syntax, explore its practical use cases, and provide multiple examples to demonstrate its functionality. We will also highlight the key differences between ROLLUP and CUBE in PostgreSQL, helping us choose the right operation based on our needs.

What is PostgreSQL ROLLUP?

ROLLUP in PostgreSQL is used to create subtotals and grand totals in the result set. It assumes a hierarchical relationship between the columns and generates only those grouping sets that make sense within that hierarchy. This is different from the CUBE subclause, which generates all possible combinations.

Syntax

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

Key Terms

• column1, column2, column3: These are the columns that will be used for grouping.
  
• aggregate(column4): This is the aggregate function we want to apply (e.g., SUM, COUNT, AVG).

PostgreSQL ROLLUP Examples

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. This will help us illustrate how ROLLUP works in various aggregation scenarios.

1. Creation of the Sample table

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

2. Insertion of values

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);

Example 1: Generating Subtotals and Grand Totals Using ROLLUP

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.

Query:

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

Output

Explanation:

This query calculates the sum of quantities for each course_name and segment, and also generates subtotals for each course_name and a grand total across all courses and segments.

Example 2: Partial ROLLUP for Grouping by Segment

In this example, we will modify the ROLLUP to generate subtotals within each segment and across all segments. This demonstrates how ROLLUP allows us to apply partial aggregation.

Query:

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

Output

Explanation:

This query first groups data by segment and then by course_name within each segment, generating subtotals for each course_name and a total for the entire segment.

Conclusion

The PostgreSQL ROLLUP clause is an important tool for generating hierarchical aggregations in our reports. By using ROLLUP, we can easily calculate subtotals and grand totals for grouped data, making it a must-know feature for any PostgreSQL user working with summary reports. Additionally, it simplifies complex queries by eliminating the need for manual aggregation, saving time and effort in data analysis.