PostgreSQL ROLLUP

Summary: In this tutorial, you’ll learn how to use the PostgreSQL ROLLUP to create multiple levels of aggregation.

PostgreSQL ROLLUP Overview #

In PostgreSQL, the GROUP BY clause groups rows into multiple groups and you can apply an aggregate function to each group. However, the GROUP BY clause can only generate a single level of aggregation.

To generate multiple levels of aggregation, you use the ROLLUP option of the GROUP BY clause.

Here’s the syntax of the GROUP BY with the ROLLUP option:

SELECT
  column1,
  column2,
  aggregate_function(column3)
FROM
  table_name
GROUP BY
  ROLLUP(column1, column2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the ROLLUP(column1, column2) generates the three groupings:

1. (column1, column2): Regular groupings that include aggregate data by values of both column1 and column2.
2. (column1, NULL): Subtotal per column1.
3. (NULL, NULL): Grand total.

The ROLLUP assumes a hierarchical relationship between column1 and column2:

column1 > column2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The ROLLUP can help generate subtotal and grand total rows hierarchically.

PostgreSQL ROLLUP Examples #

We’ll use the inventory_reports table to demonstrate how the ROLLUP works.

CREATE TABLE inventory_reports(
     warehouse VARCHAR NOT NULL,
     brand VARCHAR NOT NULL,
     quantity INT NOT NULL
);

INSERT INTO inventory_reports(warehouse, brand, quantity)
VALUES
('San Jose', 'Apple', 100),
('San Francisco', 'Apple', 200),
('Texas', 'Apple', 300),
('San Jose', 'Samsung', 50),
('San Francisco', 'Samsung', 100),
('Texas', 'Samsung', 150)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

   warehouse   |  brand  | quantity
---------------+---------+----------
 San Jose      | Apple   |      100
 San Francisco | Apple   |      200
 Texas         | Apple   |      300
 San Jose      | Samsung |       50
 San Francisco | Samsung |      100
 Texas         | Samsung |      150Code language: plaintext (plaintext)

Using ROLLUP with one column #

The following query uses the GROUP BY clause to calculate the total quantity for each warehouse:

SELECT
  warehouse,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  warehouse
ORDER BY
  warehouse;Code language: PHP (php)

Output:

   warehouse   | total_quantity
---------------+----------------
 San Francisco |            300
 San Jose      |            150
 Texas         |            450

To include the grand total row, you can use the ROLLUP as follows:

SELECT
  warehouse,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  ROLLUP(warehouse)
ORDER BY
  warehouse;Code language: PHP (php)

Output:

   warehouse   | total_quantity
---------------+----------------
 San Francisco |            300
 San Jose      |            150
 Texas         |            450
 NULL          |            900   -> grand totalCode language: PHP (php)

In this example, the ROLLUP(warehouse) adds one more row that includes the total quantity for all warehouses.

Using ROLLUP with multiple columns #

The following query uses the GROUP BY clause to generate total quantity by warehouse and brand:

SELECT
  warehouse,
  brand,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  warehouse,
  brand
ORDER BY
  warehouse,
  brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   warehouse   |  brand  | total_quantity
---------------+---------+----------------
 San Francisco | Apple   |            200
 San Francisco | Samsung |            100
 San Jose      | Apple   |            100
 San Jose      | Samsung |             50
 Texas         | Apple   |            300
 Texas         | Samsung |            150Code language: plaintext (plaintext)

The output does not show the subtotal by warehouse and total quantity by all warehouses.

To generate subtotal and grand total, you use the ROLLUP option as follows:

SELECT
  warehouse,
  brand,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  ROLLUP(warehouse, brand)
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   warehouse   |  brand  | total_quantity
---------------+---------+----------------
 San Francisco | Apple   |            200
 San Francisco | Samsung |            100
 San Francisco | NULL    |            300   --> subtotal
 San Jose      | Apple   |            100
 San Jose      | Samsung |             50
 San Jose      | NULL    |            150   --> subtotal
 Texas         | Apple   |            300
 Texas         | Samsung |            150
 Texas         | NULL    |            450   --> subtotal
 NULL          | NULL    |            900   --> grand totalCode language: plaintext (plaintext)

In this example, the ROLLUP(warehouse, brand):

• Generates the total quantity by warehouse and brand, which is the same as the previous example that does not use the ROLLUP option.
• Adds a subtotal row for each warehouse (NULL in the brand column).
• Adds a grand total row (NULL in both warehouse and brand columns).

If you change the order of the warehouse and brand columns in the ROLLUP, the result set will be different. For example:

SELECT
  brand,
  warehouse,
  SUM(quantity) AS total_quantity
FROM
  inventory_reports
GROUP BY
  ROLLUP(brand, warehouse)
ORDER BY
  brand NULLS LAST,
  warehouse NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Now the ROLLUP assumes the following hierarchy:

brand > warehouseCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

  brand  |   warehouse   | total_quantity
---------+---------------+----------------
 Apple   | San Francisco |            200
 Apple   | San Jose      |            100
 Apple   | Texas         |            300
 Apple   | NULL          |            600  -> subtotal
 Samsung | San Francisco |            100
 Samsung | San Jose      |             50
 Samsung | Texas         |            150
 Samsung | NULL          |            300  -> subtotal
 NULL    | NULL          |            900  -> totalCode language: plaintext (plaintext)

Summary #

• Use the ROLLUP to generate multiple levels of aggregation.