SQL Grouping Sets

Summary: in this tutorial, you’ll learn how to use the SQL GROUPING SETS to group data into multiple groupings in the same query.

Introduction to the SQL GROUPING SETS #

In SQL, the GROUP BY clause allows you to group rows into groups based on values in one or more columns. However, it only supports a single grouping.

If you want to create multiple groupings within the same query, you can use the GROUPING SETS option of the GROUP BY clause.

The GROUPING SETS clause is like having multiple GROUP BY clauses within the same query.

Here’s the basic syntax of the GROUP BY clause with the GROUPING SETS:

SELECT
  column1,
  column2,
  aggregate_function (column_3)
FROM
  table_name
GROUP BY
  GROUPING SETS (
      (column1, column2), 
      (column1), 
      (column2), 
      ()
  );Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, use the GROUPING SETS to specify one or more groupings.
Second, use a tuple to represent each grouping.

The GROUPING SETS includes four groups:

The tuple (column1, column2) is a grouping that groups the rows by column1 and column2.
The tuple (column1) is a grouping that groups the rows by the column1.
The tuple (column2) is a grouping that groups the rows by the column2.
The empty tuple () represents the grand total.

In practice, you’ll find the GROUPING SETS helpful in calculating subtotals and grand totals.

SQL GROUPING SETS example #

We’ll use the following salary_reports table to show how the GROUPING SETS works:

State	Job	Salary
California	IT	150000.00
California	Marketing	130000.00
Texas	IT	100000.00
Texas	Marketing	80000.00

SQL Script for creating the salary_reports table

DROP TABLE IF EXISTS salary_reports;

CREATE TABLE salary_reports (
  state varchar(255) NOT NULL,
  job varchar(255) NOT NULL,
  salary DEC(11, 2)
);

INSERT INTO
  salary_reports (state, job, salary)
VALUES
  ('California', 'IT', 150000),
  ('California', 'Marketing', 130000),
  ('Texas', 'IT', 100000),
  ('Texas', 'Marketing', 80000);

SELECT
  *
FROM
  salary_reports;Code language: SQL (Structured Query Language) (sql)

The following query uses a GROUPING SETS to calculate :

The total salary for each state.
The total salary for each job.
The salary for the combination of state and job.
Grand total salary for all rows

SELECT
  state,
  job,
  SUM(salary) AS total_salary
FROM
  salary_reports
GROUP BY
  GROUPING SETS ((state, job), (state), (job), ())
ORDER BY
  state,
  job;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   state    |    job    | total_salary
------------+-----------+--------------
 California | IT        |    150000.00
 California | Marketing |    130000.00
 California | NULL      |    280000.00
 Texas      | IT        |    100000.00
 Texas      | Marketing |     80000.00
 Texas      | NULL      |    180000.00
 NULL       | IT        |    250000.00
 NULL       | Marketing |    210000.00
 NULL       | NULL      |    460000.00Code language: plaintext (plaintext)

In this example, we use the GROUPING SETS to create four groupings:

The grouping (state, job) groups salaries by state and job:

   state    |    job    | total_salary
------------+-----------+--------------
 California | IT        |    150000.00
 California | Marketing |    130000.00
 Texas      | IT        |    100000.00
 Texas      | Marketing |     80000.00Code language: plaintext (plaintext)

The grouping (state) groups salaries by states:

   state    |    job    | total_salary
------------+-----------+--------------
 California | NULL      |    280000.00
 Texas      | NULL      |    180000.00Code language: plaintext (plaintext)

The jobcolumn is NULL.

The grouping (job) groups salaries by jobs:

   state    |    job    | total_salary
------------+-----------+--------------
 NULL       | IT        |    250000.00
 NULL       | Marketing |    210000.00Code language: plaintext (plaintext)

The state column is NULL.

The grouping () represents the grand total salary:

   state    |    job    | total_salary
------------+-----------+--------------
 NULL       | NULL      |    460000.00Code language: plaintext (plaintext)

Both state and job columns are NULL.

Summary #

Use the SQL GROUPING SETS to group data into multiple groupings.

Quiz #

Databases #

Was this tutorial helpful ?