Home » PostgreSQL Tutorial » PostgreSQL GROUPING SETS

PostgreSQL GROUPING SETS

Summary: in this tutorial, you’ll learn how to use the PostgreSQL GROUPING SETS to create multiple groupings within the same query.

PostgreSQL GROUPING SETS Overview #

In PostgreSQL, the GROUPING SETS is an advanced feature of the GROUP BY clause that allows you to create multiple groupings within the same query.

The GROUP BY clause allows you to create a single grouping. To create multiple groupings, you can create multiple queries using GROUP BY clauses.

Alternatively, you combine multiple queries with GROUP BY clauses using UNION operators.

The GROUPING SETS enables multiple groupings within the same query without using multiple queries or the UNION operator.

Here’s the general syntax of the GROUPING SETS:

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

In this syntax:

  • First, use the GROUPING SETS in the GROUP BY clause.
  • Second, specify a list of grouping sets in the GROUPING SETS.

The query creates four grouping sets:

  • The (column1, column2) defines a grouping that groups rows by column1 and column2.
  • The (column1) defines a grouping that groups rows by column1.
  • The (column2) defines a grouping that groups rows by column2.
  • The () defines a grouping that represents the aggregation of overall rows.

Note that you can have more or less grouping sets. For example, the following query will return three grouping sets:

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

PostgreSQL GROUPING SETS Example #

We’ll use the following inventory_reports to illustrate how the GROUPING SETS works.

Here’s the script for generating inventory_reports table:
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)

Output:

   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)

The following query uses the GROUPING SETS to return multiple groupings:

SELECT
  warehouse,
  brand,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse, brand), (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
---------------+---------+-------
 San Francisco | Apple   |   200
 San Francisco | Samsung |   100
 San Francisco | NULL    |   300
 San Jose      | Apple   |   100
 San Jose      | Samsung |    50
 San Jose      | NULL    |   150
 Texas         | Apple   |   300
 Texas         | Samsung |   150
 Texas         | NULL    |   450
 NULL          | Apple   |   600
 NULL          | Samsung |   300
 NULL          | NULL    |   900Code language: plaintext (plaintext)

In this example, the GROUPING SETS creates four groupings:

The grouping (warehouse, brand) groups inventory by warehouse and brand:

   warehouse   |  brand  | total
---------------+---------+-------
 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 grouping (warehouse) groups inventory by warehouse:

   warehouse   |  brand  | total
---------------+---------+-------
 San Francisco | NULL    |   300
 San Jose      | NULL    |   150
 Texas         | NULL    |   450Code language: plaintext (plaintext)

The grouping (brand) groups inventory by brand:

   warehouse   |  brand  | total
---------------+---------+-------
 NULL          | Apple   |   600
 NULL          | Samsung |   300Code language: plaintext (plaintext)

The grouping () represents the total inventory of all warehouses and brands:

   warehouse   |  brand  | total
---------------+---------+-------
 NULL          | NULL    |   900Code language: plaintext (plaintext)

Selecting Groupings #

The following example uses the GROUPING SETS to create two groupings:

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

Try it

Output:

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

In this example, the GROUPING SETS creates two groupings:

  • The grouping (warehouse, brand) groups inventory by warehouse and brand.
  • The grouping () represents the total inventory of all warehouses and brands.

PostgreSQL GROUPING Function #

The GROUPING function takes a column and returns 1 if it is aggregated and 0 otherwise.

GROUPING(column_name)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example:

SELECT
  warehouse,
  brand,
  GROUPING(warehouse) warehouse_grouping,
  GROUPING(brand) brand_grouping,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse), (brand), ())
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   warehouse   |  brand  | warehouse_grouping | brand_grouping | total
---------------+---------+--------------------+----------------+-------
 San Francisco | NULL    |                  0 |              1 |   300
 San Jose      | NULL    |                  0 |              1 |   150
 Texas         | NULL    |                  0 |              1 |   450
 NULL          | Apple   |                  1 |              0 |   600
 NULL          | Samsung |                  1 |              0 |   300
 NULL          | NULL    |                  1 |              1 |   900Code language: plaintext (plaintext)

In the output:

  • The rows with brand_grouping 1 show the total inventory of all brands by warehouses.
  • The rows with warehouse_grouping 1 show the total inventory of all warehouses by brands.
  • The rows with warehouse_grouping and brand_grouping 1 show the total inventory of all brands and warehouses.

You can use the GROUPING function in the HAVING clause to select rows aggregated by a column.

For example, the following query uses the GROUPING function to return the rows aggregated by brands:

SELECT
  warehouse,
  brand,
  GROUPING(warehouse) warehouse_grouping,
  GROUPING(brand) brand_grouping,
  SUM(quantity) total
FROM
  inventory_reports
GROUP BY
  GROUPING SETS ((warehouse), (brand), ())
HAVING
  GROUPING(brand) = 1
ORDER BY
  warehouse NULLS LAST,
  brand NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   warehouse   | brand | warehouse_grouping | brand_grouping | total
---------------+-------+--------------------+----------------+-------
 San Francisco | NULL  |                  0 |              1 |   300
 San Jose      | NULL  |                  0 |              1 |   150
 Texas         | NULL  |                  0 |              1 |   450
 NULL          | NULL  |                  1 |              1 |   900Code language: plaintext (plaintext)

Summary #

  • Use the PostgreSQL GROUPING SETS to create multiple groupings within the same query.
  • The GROUPING function returns 1 if the column is aggregated or 0 otherwise.
Was this tutorial helpful ?
PostgreSQL HAVING
Previously
PostgreSQL HAVING
Up Next
PostgreSQL ROLLUP
PostgreSQL ROLLUP