Databases are used to present raw data in a structured form of data and store it for later use. Getting information from the structured form of the data is easy compared to the raw form and the user can also generate reports from that. The ROLLUP clause is used inside the GROUP BY clause to get multidimensional reports on the PostgreSQL database.

This guide will explain the working of ROLLUP in PostgreSQL.

How Does ROLLUP Work in PostgreSQL?

ROLLUP is the sub-clause of the GROUP BY clause and it assumes that there is a hierarchy in the columns available in the table. It is mainly used to generate subtotals of the columns for reporting purposes. It does not display all the subsets for the columns as it assumes the hierarchy among the columns. It will display only four grouping sets as mentioned below for the ROLLUP(column_1, column_2, and column_3) query:

(column_1, column_2, column_3)
   (column_1, column_2)
   (column_1)
   ()

Syntax

The following is the syntax for using the ROLLUP clause in the PostgreSQL database:

SELECT
  column_1,
  column_2,
  column_3,
  aggregate(column_4)
 FROM
  table_name
   GROUP BY
  ROLLUP (column_1, column_2, column_3);

Here in the above query:

- The SELECT statement will select the list of columns with the column on which the aggregate function will be applied.
- The table_name for the selected columns will be mentioned in the FROM keyword.
- GROUP BY clause will contain its sub-clause which is ROLLUP to select columns for generating grouping subsets.

It is also allowed to use partial ROLLUP in the PostgreSQL database:

SELECT
  column_1,
  column_2,
  column_3,
  aggregate(column_4)
 FROM
  table_name
   GROUP BY
  column_1, 
  ROLLUP (column_2, column_3);

The partial ROLLUP query will take one column which will be used inside the GROUP BY clause to apply ROLLUP on the other columns to reduce the number of subsets.

Example 1: ROLLUP in PostgreSQL

Use the following query to get the data from the sales table with the ORDER BY clause to sort the result according to the columns:

SELECT * FROM sales
 ORDER BY continent, country, city;

Running the above query will display the data from the table sorted according to the columns from start to end:

The following query uses the ROLLUP clause on the sales table to generate multiple groups for its columns:

SELECT continent, country, city, sum(units_sold)
 FROM sales
   GROUP BY ROLLUP (continent, country, city);

The above code explains that the data will be selected from the sales table containing columns continent, country, and city. The last column will be placed inside the aggregate function to perform sum() on it according to previously given columns. ROLLUP clause will simply generate grouping sets according to the columns mentioned inside it.

Output

Running the above query generates all the subsets containing the sum for the complete data and also the sum of each continent individually. It also generates the sum of units_sold for each country and city from the sales table:

Example 2: Partial ROLLUP in PostgreSQL

The following query uses a partial ROLLUP subclause to reduce the number of subsets:

SELECT continent, country, city, sum(units_sold)
 FROM sales
   GROUP BY continent,
   ROLLUP (country, city);

The change from the previous example is that it takes the continent column from the ROLLUP clause and places it under the GROUP BY clause. It will reduce the column displaying the sum of all units_sold in the sales table.

Output

The following screenshot displays the tables with multiple grouping sets of the sales table:

That’s all about the working of ROLLUP in PostgreSQL.

Conclusion

ROLLUP is the sub-clause of the GROUP BY clause used in the structured query language to generate multiple grouping sets of the table. It assumes the hierarchy among the columns in the table to reduce the number of subsets for the columns in the table. PostgreSQL allows the use of ROLLUP and partial ROLLUP in the query to generate reports accordingly. This guide has explained the working of the ROLLUP clause in the PostgreSQL database.