Postgres Analytics – Group By Grouping Sets, Rollup, and Cube

I can teach you analytics! Postgres is built for analytics, and this blog will brilliantly show you something amazing you have probably never seen before. You will witness the Group By Grouping Sets, Rollup, and Cube, three techniques you will add to your analytic arsenal.

GROUP BY GROUPING SETS, ROLLUP, and CUBE are particularly useful when aggregating data by multiple dimensions in a single query. It avoids the need for multiple separate queries to compute totals for different levels of aggregation.

The GROUP BY GROUPING SETS provides basic dimensions, which are taken further by the GROUP BY ROLLUP and then taken to the limit by GROUP BY CUBE.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the World. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website. I hope to meet you and say thanks in my next class at your company:)

https://coffingdw.com/education/

Normal Group By Statement

Normally, we must perform separate GROUP BY statements to analyze data from different perspectives. Below are two separate queries on the same table to see the data calculated by sum per product_id and month. Watch next as we use advanced techniques to combine groupings.

Group by Grouping Sets

Using GROUP BY GROUPING SETS in a single query is advantageous over running three separate queries because it enhances efficiency, simplifies code maintenance, potentially improves performance through optimized execution, and offers flexibility in dynamic grouping. It’s a powerful SQL feature for efficiently and effectively summarizing data across multiple dimensions.

Below, we have grouped our data by product_id, month, and year. Notice in the top row that we made a yearly total of 862404.35. The yearly calculation has null values in the product_id and month.

If you were to add up both months below, you would see that in September, we made 418769.36, and in October, it was 443634.99. When you combine both months, we will be back at 862404.35. The month calculation has null values in the product_id and year.

If you add up what we made for product_id 1000, 2000, and 3000, they add up to 862404.35. The product_id calculations have null values in the month and year.

GROUP BY GROUPING SETS is great when aggregating data by multiple dimensions in a single query.

Supercharged GROUP BY GROUPING SETS Example

In the example below, we are grouping the dimensions of year, quarter, region, salesperson, and day of the week. We also use a case statement in the ORDER BY clause at the end of the query, so our data comes out exactly as we want. What a query!

Line two uses the EXTRACT command to pull the year from the sale date. In line three, we use the TO_CHAR command to get the quarter. In line six, we use the TO_CHAR command again to determine the day of the week. Notice that we use the CAST command on the daily sales average to get two decimal places.

The query was run on the greatest query tool in the World, Nexus.

GROUP BY ROLLUP

A GROUP BY ROLLUP creates a hierarchical data rollup, generating subtotal rows from the most detailed level to the grouping specified. The rollup generates more rows in the result set due to the hierarchical nature, including subtotal rows for each level of aggregation up to the grand total.

The example below shows the grandtotal on the first row and the sum of sales for each product, plus product and month for all years, and product and month per year. We would have received more rows had there been multiple years of data.

Super Duper Charged GROUP BY ROLLUP Example

In the example below, we are grouping the dimensions of year, quarter, region, salesperson, and day of the week. Notice we are doing a CAST to change the data type for the average. We also use a case statement in the ORDER BY clause at the end of the query, so our data comes out exactly as we want.

Because the answer set returns 151 rows, I will show it in a table below the example.

GROUP BY CUBE

A GROUP BY CUBE creates a hierarchical data rollup, generating more detailed subtotal rows than the ROLLUP. The Cube generates more rows in the result set due to the hierarchical nature, including subtotal rows for each level of aggregation up to the grand total.

The example below shows the grandtotal on the first row and the sum of sales for each product for all years and each year, product and month for all years, product and month per year, and each month for all years. We would have received more rows had there been multiple years of data.

Postgres Pulsating GROUP BY CUBE Example

In the example below, we are grouping the dimensions of year, quarter, region, salesperson, and day of the week. Notice we are doing a CAST to change the data type for the average. We also use a case statement in the ORDER BY clause at the end of the query, so our data comes out exactly as we want.

Because the answer set returns a whopping 880 rows, I will show it in a table below the example.