From the course: PostgreSQL: Advanced Queries

Include overall aggregates with ROLLUP - PostgreSQL Tutorial

From the course: PostgreSQL: Advanced Queries

Start my 1-month free trial Buy for my team

Include overall aggregates with ROLLUP

“

- [Instructor] When analyzing data using a spreadsheet application like Excel, it's common to include total rows that sum up all of the values within a group. In the SQL queries that we've looked at so far, the group by a clause has returned statistics for each individual group, but we don't get those same kind of totals across multiple groups. There is a way to do this in a query though with the rollup option. I'll start with a grouping query that gathered some statistics about the two trees, olive oil products. (keyboard clicking) This query will pull the category ID and product name columns from the inventory.products table, and we'll also calculate some columns with the account of all of the rows, the minimum price, the maximum price, and the average price. And then grouping all of the rows based off of the category ID first, followed by a subgroup for each product name, and then we'll order the data based off of the category ID's and then the product name so that everything is in a consistent state. So, let's go ahead and run this query and take a look at the results. This returns 31 groups, one for each product category and oil type or product name. We also get the lowest, highest and average prices of the products within each of these groups. Now, in order to get subtotals and overall totals, all we need to do is add the rollup keyword into the group by clause. On line number eight, I'll come here right after the keywords group by, and I'll add in rollup. And in order to get this to work, we also need to have the group by category ID and product name values wrapped inside a parentheses. So I'll go ahead and add those in as well. Now when I execute the query, you'll see that we get a total of 35 rows now, whereas before we had 31. So, we've gained four new rows in this result set with that small change to the query. Let's take a look at now what this query is showing. At the top we have the data for category number one and all of the oil types that it contains. This part hasn't changed at all. Then I'll scroll down until I get to line number 19. You'll find that this row has a no value for the product name, but it's in category ID number one. This row is a subtotal for all of the products in category one. It shows the category number one has a total of 89 products across all product names. Here is the lowest price of 899 across all of those products. The highest price is 2799 across all of those products, and here is the average price across all of the products in category one. After this, we have the lines that show each product name within category ID number two. See here we have category two, basil infused, extra-virgin oil, chili infused oil, and so on. Let me scroll through the list until I get to line number 26. So, here is a subtotal for category ID number two. It says no here for the product name. In category number two we have a total of 18 products across all product names. There is the lowest price, the highest price and the average price for all of the products in category two. Then we have category three and the breakout for the individual products there. And I'll scroll down to the bottom where I have a subtotal for all of the products in category three on line number 34. It has a total of seven products and we have the lowest, highest and average price for that. Finally, at the very end of the results, we have a grand total row for the entire dataset. Across all categories and product names there are a total of 114 products. The lowest price product across the entire inventory is 699, the highest price product is 2799, and the average of all products is 1759. So, without the rollup keyword you'd have to write out separate queries with different grouping levels to get to these kinds of summary calculations. With the rollup keyword added to your group by clause though, you can include these summary rows, the display subtotals, and grand totals for each of the groups. This allows you to get to this useful information with a single query, which can speed up reporting tasks and have less of an impact on database operations.

Contents