Grouping Sets in PostgreSQL

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.