PostgreSQL – ROLLUP

The ROLLUP operation is used in conjunction with the GROUP BY clause to generate a result set that includes aggregated data for various levels of a specified grouping columns hierarchy. It’s similar to the CUBE operation, but it provides a more structured way of obtaining summary data for different hierarchical levels.

The basic syntax of using ROLLUP in a query is as follows:

SELECT column_list, aggregate_function(column) FROM table_name GROUP BY ROLLUP (column1, column2, ...);

Here’s a breakdown of the components:

• SELECT column_list: This is the list of columns you want to retrieve in the result set. It can include both the columns you are using for grouping and the columns for which you want to apply aggregate functions.
• aggregate_function(column): This is an aggregate function (like SUM, AVG, COUNT, etc.) that you want to apply to the specified column.
• table_name: The name of the table from which you want to retrieve data.
• GROUP BY ROLLUP (column1, column2, ...): This is where you specify the columns that you want to use for grouping using the ROLLUP operation. The query will generate aggregated data for various hierarchical levels of these columns.

Example: Suppose you have a sales table with columns: “product”, “category”, “region”, and “amount”. You want to obtain aggregated data for total sales amount across different hierarchical levels of product, category, and region. You can use the ROLLUP operation as follows:

SELECT product, category, region, SUM(amount) AS total_sales FROM sales GROUP BY ROLLUP (product, category, region);

Above query will generate a result set that includes aggregated sales data for different hierarchical levels of “product,” “category,” and “region.” It will provide subtotals for each level of the hierarchy, ultimately summarizing the sales data in a structured manner.

ROLLUP is particularly useful when you want to analyze data across various levels of a hierarchy, such as different levels of product categories and subcategories. It provides a more focused approach compared to the broader combinations generated by the CUBE operation.