PostgreSQL — Grouping Sets

I once had a use case where I needed to aggregate data based on a timestamp column in PostgreSQL for daily, weekly, and monthly reports, along with an overall total. Initially, I thought I could write separate queries for each aggregation, but soon realized this approach wasn’t sustainable.

The repetitive task became hard to maintain, and performance suffered due to the large dataset and multiple table scans. As more groupings were added, the queries slowed down even more.

Get Nishaanth S P’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

I needed a better solution — one that could handle all these aggregations in a single query without impacting performance. That’s when Grouping Sets came to the rescue.

Preparing Dataset

For this article, I’m using an employees dataset with ~1M records. The table structure is as shown below.

You can create a similar dataset by following this article. The only change is replacing the age column with the age_category values. The age_category column contains random values from the list:

['<18', '18–22', '22–30', '30–50', '>50']

Use Case

Let’s say we need the total count of employees and the total salary paid:

1. At an overall level.
2. At a department level.
3. At an age category level.

Usual Solution

To achieve this, we’d need three separate queries:

Overall Totals

postgres=# SELECT COUNT(*) employee_count, SUM(salary) total_salary
FROM employees;
 employee_count | total_salary 
----------------+--------------
         999974 | 104979369102
(1 row)

Department-Level Totals

postgres=# SELECT COUNT(*) employee_count, SUM(salary) total_salary, department
FROM employees
GROUP BY department;
 employee_count | total_salary | department 
----------------+--------------+------------
         110760 |  11648842743 | BIO
         110934 |  11647965579 | CIVIL
         111040 |  11649527795 | CSE
         110847 |  11636897402 | ECE
         111413 |  11697070354 | EEE
         111250 |  11679867736 | EIE
         111126 |  11636522921 | ICT
         110953 |  11647115798 | IT
         111651 |  11735558774 | MECH
(9 rows)

Age-Category Totals

postgres=# SELECT COUNT(*) employee_count, SUM(salary) total_salary, age_category
FROM employees
GROUP BY age_category;
 employee_count | total_salary | age_category 
----------------+--------------+--------------
         199008 |  20881070307 | 18-22
         199514 |  20967685456 | 22-30
         199964 |  20975162568 | 30-50
         200999 |  21078175369 | <18
         200489 |  21077275402 | >50
(5 rows)

While this works, you end up scanning the same data multiple times, increasing resource usage and execution time.

Solution with GROUPING SETS

Now let’s simplify this using Grouping Sets:

postgres=# SELECT COUNT(*) employee_count, SUM(salary) total_salary, department, age_category
FROM employees
GROUP BY GROUPING SETS((), (department), (age_category));
 employee_count | total_salary | department | age_category 
----------------+--------------+------------+--------------
         999974 | 104979369102 |            | 
         110760 |  11648842743 | BIO        | 
         111040 |  11649527795 | CSE        | 
         111250 |  11679867736 | EIE        | 
         111126 |  11636522921 | ICT        | 
         111651 |  11735558774 | MECH       | 
         111413 |  11697070354 | EEE        | 
         110953 |  11647115798 | IT         | 
         110934 |  11647965579 | CIVIL      | 
         110847 |  11636897402 | ECE        | 
         199514 |  20967685456 |            | 22-30
         199008 |  20881070307 |            | 18-22
         200999 |  21078175369 |            | <18
         200489 |  21077275402 |            | >50
         199964 |  20975162568 |            | 30-50
(15 rows)

Query Breakdown

The GROUPING SETS clause specifies the combinations:

1. () → Overall totals (no grouping).
2. (department) → Totals by department.
3. (age_category) → Totals by age category.

In a single query, you’ve achieved the same results as the three separate queries.

Result

The query produces rows for all grouping levels:

• Overall totals: Rows with NULL for both department and age_category.
• Department totals: Rows with age_category = NULL.
• Age-category totals: Rows with department = NULL.

Why GROUPING SETS is Better?

If you used separate queries, the result would be equivalent to:

postgres=# SELECT COUNT(*) employee_count, SUM(salary) total_salary, NULL as department, NULL as age_category
FROM employees
UNION ALL
SELECT COUNT(*) employee_count, SUM(salary) total_salary, department, NULL as age_category
FROM employees
GROUP BY department
UNION ALL
SELECT COUNT(*) employee_count, SUM(salary) total_salary, NULL as department, age_category
FROM employees
GROUP BY age_category;
 employee_count | total_salary | department | age_category 
----------------+--------------+------------+--------------
         999974 | 104979369102 |            | 
         199514 |  20967685456 |            | 22-30
         200489 |  21077275402 |            | >50
         200999 |  21078175369 |            | <18
         199008 |  20881070307 |            | 18-22
         199964 |  20975162568 |            | 30-50
         110760 |  11648842743 | BIO        | 
         111040 |  11649527795 | CSE        | 
         111250 |  11679867736 | EIE        | 
         111126 |  11636522921 | ICT        | 
         111651 |  11735558774 | MECH       | 
         111413 |  11697070354 | EEE        | 
         110953 |  11647115798 | IT         | 
         110934 |  11647965579 | CIVIL      | 
         110847 |  11636897402 | ECE        | 
(15 rows)

But with Grouping Sets, PostgreSQL computes everything in a single scan, saving time and resources.

Solution for my actual problem

Now that we saw the concept of grouping sets, let’s see how this helped the challenge of aggregating data based on daily, weekly, and monthly groupings. Here’s the query skeleton I used to solve the problem:

SELECT SUM(value_column) AS value, 
    date_column::DATE AS daily_date, 
    date_trunc('week', date_column)::DATE AS start_of_week_date,
    date_trunc('month', date_column)::DATE AS start_of_month_date
FROM myschema.mytablename
WHERE date_column >= '2020-01-01'::TIMESTAMPTZ
GROUP BY GROUPING SETS ((), (daily_date), (start_of_week_date), (start_of_month_date));

Breakdown of the Query

• SUM(value_column): This calculates the total value of the value_column.
• date_column::DATE AS daily_date: This groups the data by the specific date (day-wise).
• date_trunc(‘week’, date_column)::DATE AS start_of_week_date: This groups the data by the start of each week.
• date_trunc(‘month’, date_column)::DATE AS start_of_month_date: This groups the data by the start of each month.
• GROUPING SETS ((), (daily_date), (start_of_week_date), (start_of_month_date)): This is where the magic happens. It allows you to compute the total value for all combinations of these groupings in a single query.

Why This Works

By using Grouping Sets, I was able to calculate daily, weekly, and monthly aggregates, along with the overall total, all in one go — without having to write multiple queries or deal with slow performance. It simplified the logic, improved query efficiency, and made the whole process much more manageable.

Things to Consider Before Using GROUPING SETS in PostgreSQL

While Grouping Sets is a powerful feature, it’s essential to assess whether it’s the right fit for your use case. Here are some things to keep in mind:

1. PostgreSQL Version: Ensure your PostgreSQL version supports Grouping Sets (introduced in PostgreSQL 9.5). If you’re on an older version, this feature won’t work, and you may need to upgrade.
2. Query Complexity: If your aggregations are simple and straightforward, sticking to a regular GROUP BY might be enough.
3. Performance on Large Datasets: Grouping Sets in PostgreSQL processes multiple groupings in a single scan, which is efficient. However, as the dataset grows and grouping combinations increase, the query might still take significant time and resources. Always test with EXPLAIN ANALYZE to check performance.
4. Code Readability: While Grouping Sets simplifies logic by reducing multiple queries into one, it can look complex to developers unfamiliar with the feature. Adding comments or breaking down the query can help make it easier to understand.

What’s next — Cube and Rollup

After discovering Grouping Sets, you might wonder what comes next in optimizing your aggregation queries. Two powerful extensions of Grouping Sets are Cube and Rollup.

• Cube: It’s an extension of Grouping Sets that generates all possible combinations of your grouping columns. If you need every possible aggregation (e.g., daily, weekly, monthly, daily+weekly, etc.), Cube takes care of that automatically, saving you the effort of defining each combination manually.
• Rollup: It’s similar to Cube, but it focuses on hierarchical groupings. It will give you aggregations starting from the most detailed level and roll up to higher levels, providing a more structured aggregation flow.

Conclusion

And there you have it — Grouping Sets to the rescue! No more writing multiple queries just to get your daily, weekly, monthly, and total aggregations. It’s like finally finding the shortcut to a task you’ve been doing the long way for too long.

With Grouping Sets, you can handle multiple aggregations in one go, and keep your queries clean and fast. It’s the tool you didn’t know you needed, but now that you do, you’ll wonder why you ever did it any other way.

And if you’re feeling adventurous, you can take it further with Cube and Rollup for even more power. But for now, enjoy the peace of not writing repetitive queries and know that your SQL game just leveled up!

Happy querying, and may your data always be grouped the way you want it!