How to use GROUPING SETS in PostgreSQL

🎉 Hey there, SQL enthusiasts! Today, we’re taking a deep dive into a supercharged feature in PostgreSQL that makes grouping data even more flexible and powerful: GROUPING SETS. So, if you’ve ever found yourself stuck with basic GROUP BY queries and wanted something more, buckle up because this is the tutorial you’ve been waiting for!

What Are GROUPING SETS?

In PostgreSQL, the GROUPING SETS clause allows you to produce a single result set that’s a union of multiple groupings. It’s like running multiple GROUP BY queries and stitching the results together, but in a much more efficient way.

When To Use GROUPING SETS

The real power of GROUPING SETS comes into play when you need to generate reports that require data to be grouped in different ways within the same query. You could achieve similar results with multiple queries and some extra processing, but why make your life harder when GROUPING SETS can do it in a jiffy?

Basic Syntax

Here’s what a basic GROUPING SETS query looks like:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS (
    (column1, column2),
    (column1),
    ()
);

Let’s Dive Into Some Examples

Remember our tv_series table? We have details like title, genre, seasons, and release_year. Let’s use that to better understand GROUPING SETS.

Example 1: Counting Series by Genre and Release Year

Let’s say you want to know both the number of TV series per genre and per release year.

SELECT genre, release_year, COUNT(*)
FROM tv_series
GROUP BY GROUPING SETS (
    (genre, release_year),
    (genre),
    (release_year),
    ()
);

Let’s breakdown what is happening with GROUPING SETS. Remember that it allows you to group rows by more than one set of columns. The query above will generate four sets of results:

1. First Set: It groups the data by both genre and release_year and counts the number of records for each pair.
2. Second Set: It groups the data by genre only, regardless of release_year.
3. Third Set: It groups the data by release_year only, without considering genre.
4. Fourth Set: An empty set () means it will count all records, regardless of genre or release_year.

See sample output below

Example 2: Sum of Seasons by Genre and Release Year

To find out the total seasons by genre and release_year, and also the grand total of seasons, you can do:

SELECT genre, release_year, SUM(seasons)
FROM tv_series
GROUP BY GROUPING SETS (
    (genre, release_year),
    (genre),
    (release_year),
    ()
);

Tips and Best Practices

1. Be Specific with Your Sets: GROUPING SETS are very powerful but can also get complex fast. It’s good practice to be as specific as possible with your sets to avoid confusing results.

2. Order Matters: While the order of columns in a traditional GROUP BY clause might not matter much, it can be critical in GROUPING SETS. Make sure you understand how your sets are being grouped.

Common Errors and Solutions

Error Message:

ERROR: each GROUPING SETS column must be in the GROUP BY clause

Cause:

A column in the GROUPING SETS is not present in the GROUP BY clause.

Solution:

Ensure that all columns listed in GROUPING SETS are also in your GROUP BY clause.

Wrapping Up

GROUPING SETS give you a powerful, flexible way to approach complex queries involving multiple levels of aggregation. It’s a tool you’ll find invaluable as you look to dig deeper into your data and produce meaningful reports. So why wait? Take these learnings and apply them to your tv_series table, or any other complex dataset you have at hand.

Until next time, happy querying! 🚀