GROUPING SETS

1. OLAP operations: GROUPING SETS

We have now come to the final OLAP extension that we will cover in this course. We are going to look at queries with the GROUP BY GROUPING SETS statements.

2. Overview of OLAP operators in SQL

We have already discussed the OLAP operators CUBE and ROLLUP. Now we introduce the most flexible operator: the GROUPING SETS operator.

3. Table renting_extended

To demonstrate this, we will again focus on the table renting_extended. We will explore how we can extract different levels of aggregation for the number of movie rentals with the GROUPING SETS operator.

4. GROUP BY GROUPING SETS

Here, we have an example of a query with a GROUPING SET statement. Within the main parentheses, we have four groups in parentheses. The first group is country and genre, the second is country, the third is genre and the last is empty. These four groups represent different levels of aggregation. The GROUPING SETS operator can be seen as a UNION over GROUP BY statements where each of the four groups in parentheses represents one GROUP BY statement. We will have a look at each of the four groups in the GROUPING SETS operator and see how the corresponding GROUP BY query looks.

5. GROUPING SETS and GROUP BY queries

First, we count the number of movie rentals for each combination of country and genre. This corresponds to the expression in the GROUPING SETS operator "open parentheses country comma genre close parentheses". In the corresponding GROUP BY query, we group the data by country and genre. The output shows one row for each combination of country and genre.

6. GROUPING SETS and GROUP BY queries

Next, we group by country. This corresponds to the expression in the GROUPING SETS operator of "country in parentheses". And here you see the corresponding GROUP BY query and its result.

7. GROUPING SETS and GROUP BY queries

Similarly, now we group by genre. This is described in GROUPING SETS by "genre in parentheses". And the corresponding GROUP BY genre query gives this result.

8. GROUPING SETS and GROUP BY queries

Lastly, we have the total aggregation. It is referred to in the GROUPING SETS operator by the empty parentheses. We get the total aggregation by counting all records in the renting_extended table which is equivalent to "select count star".

9. Notation for GROUP BY GROUPING SETS

Here, again, we have the query with GROUPING SETS operator. Its resulting table is a combination of all four queries which we just saw. With this specific query, we obtain all the information that is presented in a pivot table and this is, therefore, equivalent to a query with GROUP BY CUBE of country and genre.

10. Result with GROUPING SETS operator

This is the result of the GROUPING SETS query. Note that the rows in the result table are not necessarily corresponding to the order in the GROUPING SETS statement. The first row represents the result from the total aggregation corresponding to the empty parentheses. Remember that the NULL values in the columns country and genre show that this category was aggregated. Then we have 4 rows for each combination of country and genre. Row 6 and 7 count rentals for each country and the final two rows for each genre.

11. Calculate number of rentals and average rating

GROUPING SETS is the most flexible of the OLAP operators. We can specify which aggregations should be included in the results table. Here we calculated the number of movie rentals and also the average rating for each combination of country and genre as well as for each genre and show the first resulting rows.

12. Calculate number of rentals and average rating

Here we have the resulting table of the query, showing the number of ratings as well as the average of the ratings for each combination of country and genre and for each genre.

13. Let's practice!

Now, it's time to practice again!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.