SQL Rollup
FREE
Overview
ROLLUP in SQL is an extension of the GROUP BY clause, this SQL ROLLUP option allows the user to include extra rows that represent the subtotals, which are usually referred to as super-aggregate rows, along with a grand total row.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Introduction to the SQL ROLLUP
We can use the SQL ROLLUP option to generate multiple grouping sets using a single query. ROLLUP in SQL is a sub-clause of the GROUP BY clause, which provides a shorthand for defining multiple grouping sets. A grouping set is a set of columns that we can use to perform the GROUP BY operation.
When generating grouping sets, SQL ROLLUP assumes a hierarchy among the columns and only generates grouping sets based on this hierarchy. The ROLLUP in SQL is commonly used to calculate the aggregates of hierarchical data such as sales by year > quarter > month.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Levels of Aggregation
If n is the number of grouping columns, then ROLLUP creates n+1 levels of subtotals and grand total. Because ROLLUP removes the right-most column at each step, the column order has to be specified carefully.
For Example - ROLLUP(A, B, C) creates four groups assuming the hierarchy A > B > C, as follows:
ROLLUP removes the right-most column at each step. There are no groups for (A, C) and (B, C).
Syntax
The below given example illustrates the basic section of the SQL ROLLUP.
In the above syntax, the input column is (col1,col2), having the hierarchy col1 > col2. The SQL ROLLUP generates all grouping sets considering this hierarchy. This is the reason why we often use ROLLUP to generate the subtotals and the grand total for reporting purposes.
Turn Learning into Career Growth
Parameters
group‑expression - The SQL ROLLUP clause takes any expression that is not an aggregate or a grouping function that includes constants and column references in FROM-specified tables as a parameter.
Restrictions
GROUP BY ROLLUP does not sort the results retrieved. To sort data, an ORDER BY clause must follow the GROUP BY clause.
Example
-
Aggregating the Full Data Set
Let us consider a Employees table having the salary of employees in a multinational company. The table contains ID, Name, Gender, Salary, and Country as the fields.
ID Name Gender Salary Country 1 Ash Male 5000 USA 2 James Male 4500 India 3 Nancy Female 5500 USA 4 Sara Female 4000 India 5 Thomas Male 3500 India 6 Marie Female 5000 UK 7 Benjamin Male 6500 UK 8 Eliza Female 7000 USA 9 Gary Male 5500 UK 10 Corey Male 5000 USA Using the above GROUP BY query along with the ROLLUP option helps us to group salary by Country and Gender. ROLLUP in SQL also computes the subtotal at the Country level and Grand Total.
Output:
Country Gender TotalSalary India Female 4000 India Male 8000 India NULL 12000 UK Female 5000 UK Male 12000 UK NULL 17000 USA Female 12500 USA Male 10000 USA NULL 22500 NULL NULL 51500 -
Using SQL ROLLUP with the HAVING Clause
Using the above example of the Employees table, we can learn how to use the HAVING clause with ROLLUP to restrict the GROUP BY results.
The following query uses the GROUPING function, taking one of the GROUP BY expressions as an argument. For each row, GROUPING returns one of the following:
- 0: The column is part of the group for that row
- 1: The column is not part of the group for that row
The following query produces only those ROLLUP categories where only the Gender column is sub-totaled, based on the expression in the GROUPING function.
Output:
Country Gender TotalSalary India NULL 12000 UK NULL 17000 USA NULL 22500 NULL NULL 51500 -
How to perform a partial SQL ROLLUP?
We often use ROLLUP in SQL to perform a partial roll-up that reduces the number of subtotals calculated, as shown in the following example.
Using the above Employees table, we can try doing partial ROLLUP by making a super-aggregate summary for the Gender column, not the Country column.
Output:
Country Gender TotalSalary India Female 4000 India Male 8000 India NULL 12000 UK Female 5000 UK Male 12000 UK NULL 17000 USA Female 12500 USA Male 10000 USA NULL 22500
Conclusion
- ROLLUP in SQL aggregation is used to perform multiple levels of analysis with a single query.
- ROLLUP performs aggregations across multiple dimensions at different levels within a single SQL query as an extension to the GROUP BY clause.
- SQL ROLLUP does not sort the results retrieved. An ORDER BY clause has to be used to sort the results.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more