Previous
Written by: rJTutorial Team | Last Updated:

SQL ROLLUP

ROLLUP is a powerful extension of the GROUP BY clause that allows you to generate multiple levels of grouping within a single query. While a standard GROUP BY produces a single set of aggregates, ROLLUP goes further by automatically generating subtotals and a grand total.

In the world of data analysis and business intelligence, ROLLUP is a "heavy lifter." It enables you to create hierarchical reports—like sales by year, then month, then day—without needing to write complex UNION ALL statements to stitch different summary levels together.

Developer Tip: Think of ROLLUP as a shortcut. Instead of writing three separate queries for product totals, category totals, and grand totals, ROLLUP handles all of them in one pass over the data.

 

Syntax of SQL ROLLUP

SELECT column_name1, column_name2, aggregate_function(column_name)  
FROM table_name  
GROUP BY ROLLUP(column_name1, column_name2);
  • column_name1, column_name2: These are the columns you want to group by. The order here is critical because ROLLUP creates a hierarchy from left to right.
  • aggregate_function(): Standard math functions like SUM(), COUNT(), AVG(), MAX(), or MIN().
  • ROLLUP(): The specific keyword that tells the database engine to calculate subtotals for every level of the hierarchy specified in the parentheses.
Best Practice: Always include the columns you are "rolling up" in your SELECT statement. This ensures you can actually see which subtotal belongs to which category in your result set.

 

Key Features of SQL ROLLUP

  • Hierarchical Aggregation: It calculates aggregates moving from the most detailed level (right-most column) up to the grand total.
  • Grand Total Generation: It automatically appends a final row representing the sum of all data in the result set.
  • Reduced Code Complexity: It replaces the need for multiple GROUP BY queries joined by UNION operators, which is both cleaner to read and more efficient for the database to execute.
  • Column Sensitivity: The order of columns inside ROLLUP(A, B) produces different results than ROLLUP(B, A).
Watch Out: Because ROLLUP is hierarchical, it does not calculate every possible combination of columns. If you need every possible permutation of totals, you should use CUBE instead.

 

Example of SQL ROLLUP

Consider the following table:

Table: Sales

category product sales_amount
Electronics Laptop 1000
Electronics Phone 700
Furniture Chair 500
Furniture Table 800

Query: Compute total sales for each category and product, including subtotals

SELECT category, product, SUM(sales_amount) AS total_sales  
FROM sales  
GROUP BY ROLLUP(category, product);
Common Mistake: Forgetting that NULL values in the result set don't always mean "missing data." In a ROLLUP, a NULL in a grouping column usually signifies a subtotal or a grand total.

Output:

categoryproducttotal_sales
ElectronicsLaptop1000
ElectronicsPhone700
ElectronicsNULL1700
FurnitureChair500
FurnitureTable800
FurnitureNULL1300
NULLNULL3000

Explanation:

  1. Individual Totals: The first two rows for each category show the standard SUM for each specific product.
  2. Category Subtotal: Look at row 3. The product is NULL, and the total_sales is 1700. This is the sum of Laptop (1000) and Phone (700) within the "Electronics" category.
  3. Grand Total: The very last row has NULL for both category and product. This represents the absolute total of all sales across all categories (1700 + 1300 = 3000).
Developer Tip: You can use the COALESCE() function to make your reports more readable by replacing those NULL labels with "All Products" or "Grand Total." For example: SELECT COALESCE(category, 'All Categories') ...

 

SQL ROLLUP vs. GROUPING SETS vs. CUBE

Choosing the right grouping tool depends on the structure of your report. Here is a quick breakdown of how they compare:

FeatureROLLUPCUBEGROUPING SETS
Generates subtotals✅✅✅
Generates grand total✅✅✅
Generates all possible combinations❌✅✅ (customizable)
Best for hierarchical data✅❌✅

Use ROLLUP when your data has a natural hierarchy (e.g., Country > State > City). Use CUBE when you need to see every possible cross-section of data (e.g., sales by product regardless of region, AND sales by region regardless of product).

 

SQL ROLLUP with COUNT()

While SUM() is the most common use case, COUNT() is frequently used in inventory and HR reporting to see how items or employees are distributed across departments.

Example: Count the number of products in each category and overall

SELECT category, product, COUNT(*) AS product_count  
FROM sales  
GROUP BY ROLLUP(category, product);
Best Practice: When using ROLLUP with COUNT(), be careful if your underlying data has actual NULL values. COUNT(*) includes all rows, while COUNT(column_name) only counts non-null entries.

 

Summary

  • ROLLUP is the go-to tool for hierarchical reports that require subtotals and grand totals in a single result set.
  • The order of columns in the ROLLUP clause defines the hierarchy of the subtotals.
  • The NULL values produced in the grouping columns identify the summary rows.
  • Use cases: Perfect for financial balance sheets, sales performance dashboards, and organizational headcounts.
Previous