Grouping Sets in PostgreSQL
Introduction
When you’re querying tables in PostgreSQL, you may want to group your results into sets in order to better understand and analyze the data. Fortunately, it’s easy to define and retrieve those sets using the GROUPING SETS
clause. This clause will return results that are equivalent to what would be returned by using the GROUP BY
and UNION ALL
clauses you may be familiar with in SQL. In this article, we’ll take a closer look at the process of grouping sets in PostgreSQL and show some helpful examples of how it’s done.
Prerequisite
Before proceeding with this tutorial, make sure that PostgreSQL has already been installed and configured on your system. You’ll also need a basic understanding of SQL in order to follow along with the examples presented in this article.
PostgreSQL Sample Database
Let’s start by creating a PostgreSQL sample database that we can use in this tutorial. To create your own database, you’ll need to follow the steps outlined below:
- First, log in to your PostgreSQL shell.
- Then, create a database named
studentdb
. - Connect to the database using the psql command
\c
. - Finally, create a table named
student
and insert a few records into it.
1 | CREATE DATABASE studentdb |
After you execute this command, PostgreSQL will notify you that the database was created. The output will look like this:
1 | CREATE DATABASE |
We can then connect to our newly-created database using the command \c
as mentioned in the steps listed above:
1 2 | postgres=# \c studentdb You are now connected TO DATABASE "studentdb" AS USER "postgres". |
Once we’re connected, we can create the table named student
using the following command:
1 2 3 4 5 6 | postgres=# CREATE TABLE student( postgres(# name VARCHAR, postgres(# course VARCHAR, postgres(# age INT postgres(# ); CREATE TABLE |
Now that we’ve set up our sample database and corresponding table, we can go ahead and insert some records:
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# INSERT INTO student(name, course, age) VALUES postgres-# ('Manuel Legarda', 'Information Technology', '22'), postgres-# ('Dawsom Blake', 'Information Technology', '21'), postgres-# ('Jonathan Megan', 'Business Administration', '23'), postgres-# ('John Doe', 'Information Technology', '22'), postgres-# ('Igor Dimitri', 'Culinary', '22'), postgres-# ('John Spacey', 'Culinary', '22'), postgres-# ('Mary Higgins', 'Business Administration', '20'), postgres-# ('Lance Morgan', 'Information Technology', '21'), postgres-# ('Gina Moon', 'Information Technology', '22'), postgres-# ('Mariel Kates', 'Business Administration', '22'); INSERT 0 10 |
We receive a notification when the operation is complete: INSERT 0 10
.
We can verify that the operation was successful using the SELECT * command:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# SELECT * FROM student; name | course | age ----------------+-------------------------+----- Manuel Legarda | Information Technology | 22 Dawsom Blake | Information Technology | 21 Jonathan Megan | Business Administration | 23 John Doe | Information Technology | 22 Igor Dimitri | Culinary | 22 John Spacey | Culinary | 22 Mary Higgins | Business Administration | 20 Lance Morgan | Information Technology | 21 Gina Moon | Information Technology | 22 Mariel Kates | Business Administration | 22 (10 ROWS) |
Basic PostgreSQL grouping
In this example, we’ll show you how to perform a simple grouping of results in a PostgreSQL database using the standard GROUP BY
clause:
1 2 3 4 5 6 7 8 | SELECT name, course FROM student GROUP BY course, name; |
This SQL command will get the values of the ‘name’ and ‘course’ fields from the table ‘student’ and group them according to the sets of course
and name
.
We should get output that looks like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 | name | course ----------------+------------------------- Lance Morgan | Information Technology Manuel Legarda | Information Technology Gina Moon | Information Technology Igor Dimitri | Culinary John Doe | Information Technology Dawsom Blake | Information Technology John Spacey | Culinary Mariel Kates | Business Administration Jonathan Megan | Business Administration Mary Higgins | Business Administration (10 rows) |
GROUPING SET in PostgreSQL
The PostgreSQL GROUPING SETS
clause enables us to create various grouping sets using the same query. The basic syntax looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT SET1, SET2, SET3 FROM TABLE_NAME GROUP BY GROUPING SETS ( (SET1, SET3), (SET1), (SET2) ); |
Let’s try using this syntax to utilize GROUPING SETS
in our SQL query:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT name, course, age FROM student GROUP BY GROUPING SETS ( (name, age), (name), (course) ); |
The above SQL statement will select the specified fields from the table student
as sets and group the sets accordingly.
The output should look something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | name | course | age ----------------+-------------------------+----- Lance Morgan | | 21 Jonathan Megan | | 23 John Spacey | | 22 Dawsom Blake | | 21 Igor Dimitri | | 22 Mariel Kates | | 22 John Doe | | 22 Mary Higgins | | 20 Gina Moon | | 22 Manuel Legarda | | 22 Jonathan Megan | | John Spacey | | Manuel Legarda | | Igor Dimitri | | Dawsom Blake | | Lance Morgan | | John Doe | | Gina Moon | | Mariel Kates | | Mary Higgins | | | Business Administration | | Information Technology | | Culinary | (23 rows) |
Conclusion
When you execute queries in PostgreSQL, it’s important to get results that are clear and easy to understand. You’ve probably used GROUP BY
to group and organize your results, but you can get even better reporting from the GROUPING SETS
clause. In this article, we showed you examples of how to use GROUPING SETS
in PostgreSQL to better organize the results of a query. With our instructions and examples, you’ll be able to take your own PostgreSQL querying and reporting skills to the next level.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started