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.

After you execute this command, PostgreSQL will notify you that the database was created. The output will look like this:

We can then connect to our newly-created database using the command \c as mentioned in the steps listed above:

Once we’re connected, we can create the table named student using the following command:

Now that we’ve set up our sample database and corresponding table, we can go ahead and insert some records:

We receive a notification when the operation is complete: INSERT 0 10.

We can verify that the operation was successful using the SELECT * command:

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:

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:

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:

Let’s try using this syntax to utilize GROUPING SETS in our SQL query:

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:

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.