We have many functions for aggregating data in SQL such as AVG,MIN,MAX etc. Apart from these functions, there are sql server extensions to the group by clause which can be used to provide summarized data and give some powerful use of the data. In this post l will illustrate the operator called ROLLUP which is one those extensions for summarizing data.
Take for example the data in the screen shot below. Assume you have a customers table that has information on the gender and the education of the customers. The snapshot is the distinct gender and education from the customer table.
I want to solve below cases from the customer table;
i. write a query to get the count of each gender and total of both gender in the same result set
SELECT [Gender],COUNT(*) AS Total
FROM [dbo].[DimCustomer]
GROUP BY Gender WITH ROLLUP
With the Rollup operator, we are able to add the last summary row which has the total of F and M genders of 18484.
ii.write a query to get the count of gender and education grouping, total number of each gender and total number of all gender in the same query.
SELECT [Gender],[EnglishEducation],COUNT(*) AS Total
FROM [dbo].[DimCustomer]
GROUP BY Gender,[EnglishEducation] WITH ROLLUP
Here we can see the count of the combination of each gender and education grouping then the total of that gender (F = 9133, M = 9351)
The last row with total = 18484 is the total for all gender.
In summary, the rollup operator adds a summary row for each group specified. It also adds a summary row to the end of the result set that summarizes the entire result set. If the group by clause specifies a single group, only the final summary row is added. When you use the rollup operator, you can’t use a distinct keyword in any of the aggregate functions.
I will be writing on the other sql server extensions CUBE,GROUPING SETS and the Over clause in the future.
