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 bycolumn1
andcolumn2
. - The tuple
(column1)
is a grouping that groups the rows by thecolumn1
. - The tuple
(column2)
is a grouping that groups the rows by thecolumn2
. - 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)
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.00
Code 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.00
Code language: plaintext (plaintext)
The grouping (state)
groups salaries by states:
state | job | total_salary
------------+-----------+--------------
California | NULL | 280000.00
Texas | NULL | 180000.00
Code language: plaintext (plaintext)
The job
column is NULL
.
The grouping (job)
groups salaries by jobs:
state | job | total_salary
------------+-----------+--------------
NULL | IT | 250000.00
NULL | Marketing | 210000.00
Code language: plaintext (plaintext)
The state
column is NULL
.
The grouping ()
represents the grand total salary:
state | job | total_salary
------------+-----------+--------------
NULL | NULL | 460000.00
Code language: plaintext (plaintext)
Both state
and job
columns are NULL
.
Summary #
- Use the SQL
GROUPING SETS
to group data into multiple groupings.