In this article, we will learn how to calculate and add a subtotal in SQL queries.
Introduction
A subtotal is a figure that shows the sum of similar sets of data but it does not indicate the final total. Subtotals are mainly used in sales, finance, and accounting reports. At the same time, we can notice this figure commonly in the receipts and they commonly locate before tax.
Calculating a subtotal in SQL query can be a bit complicated than the common aggregate queries. However, SQL Server offers some GROUP BY extensions that help us to resolve this issue. In the next section of this article, we will discover these extensions which help to calculate a subtotal in SQL query.
Pre-Requirement
In the following examples of the article, we will use the SalesList sample table and the following query helps to create this example table and it will also populate some sample data to this table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE SalesList (SalesMonth NVARCHAR(20), SalesQuartes VARCHAR(5), SalesYear SMALLINT, SalesTotal MONEY) GO INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2019,60) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2020,50) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'May','Q2',2019,30) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'July','Q3',2020,10) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'November','Q4',2019,120) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'October','Q4',2019,150) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'November','Q4',2019,180) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'November','Q4',2020,120) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'July','Q3',2019,160) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2020,170) GO SELECT * FROM SalesList |
Understanding ROLLUP extension
The GROUP BY statement is used to groups the rows that have the same values in a new summary row and it is the lead actor of the aggregate queries. ROLLUP, CUBE, and GROUPING SETS are extensions of the GROUP BY statement and add the extra subtotal and grand total rows to the resultset. In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set. For example, the GROUP BY ROLLUP (SalesYear) statement only adds the grand total of all years to the result set but the GROUP BY ROLLUP (SalesYear, SalesQuartes) statement will add the following extra rows to the result set.
SalesYear,NULL -> Subtotal
NULL ,NULL -> Grand total
As the last point, related to the ROLLUP extension, the subtotal combination will depend on the passed column parameters. In order to understand this concept better, we will look at the following examples:
Example-1:
|
1 2 |
SELECT SalesYear, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear) |
We can see an extra row at the end of the result set, this row shows the grand total sales of the years without considering the sales month and quarters.
Example-2:
In this example, we will pass two different columns as a parameter to the ROLLUP. In this case, the ROLLUP adds the extra subtotals and a grand total row into the resultset.
|
1 2 |
SELECT SalesYear,SalesQuartes, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes) |
In the result set of the query, the rows that are marked with numbers 1 and 2 indicate the subtotals by the years. The row marked with the number 3 shows the grand total of the whole sales of the years.
Example-3:
In this example, we will pass 3 columns into the ROLLUP extension and then this extension will generate subtotal rows for all hierarchies.
|
1 2 |
SELECT SalesYear,SalesQuartes,SalesMonth ,SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes, SalesMonth) |
Understanding GROUPING function
The GROUPING function is used to determine whether the columns in the GROUP BY list have been aggregated. Therefore, we can use this function to identify the NULL values and replace them.
|
1 2 3 4 5 6 7 |
SELECT SalesYear, SalesQuartes, SUM(SalesTotal) AS SalesTotal , GROUPING(SalesQuartes) AS SalesQuarterGrp, GROUPING(SalesYear) AS SYearGrp FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes) |
As we can see, grouped rows are determined by GROUPING functions. Now we will use the SQL CASE statement and GROUPING function together so that we will replace NULL values with more meaningful explanations.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT CASE WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=0 THEN 'SubTotal' WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=1 THEN 'Grand Total' ELSE CAST(SalesYear AS varchar(10)) END AS SalesYear, SalesQuartes, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear,SalesQuartes) |
As a result, we added a subtotal and grand total row to the result set with help of the ROLLUP extension.
Calculate subtotal in SQL query only for one column
For some cases, we may require to add a subtotal in SQL query for only one column. In this circumstance, we can use the ROW_NUMBER() and NEWID() function together to handle this issue. The reason for this combo usage is to add a unique number to each row and then we will use that numbered row for grouping.
|
1 2 |
SELECT SalesMonth,SalesTotal , ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNumber FROM SalesList |
As a second step, we are required to work on this result set temporarily to aggregate sales amount and adding the extra subtotals rows. CTE (Common Table Expressions) can the best choice because it allows us to define temporarily named result sets.
|
1 2 3 4 5 6 7 8 9 10 |
WITH CTE AS ( SELECT SalesMonth,SalesTotal , ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNumber FROM SalesList ) SELECT RowNumber ,SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) |
In this result set, we need to remove the RowNumber column and only show the SalesMonth column and its’ subtotal rows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH CTE AS ( SELECT SalesMonth,SalesTotal , ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNumber FROM SalesList ) SELECT CASE WHEN GROUPING(RowNumber) =1 THEN 'SubTotal' ELSE SalesMonth END AS SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) |
In this data set, the row that appears as yellow is not a subtotal row, so we need to avoid that row from appearing in the resulting output of our query. To do this, we will again use the GROUPING function and filter this row.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH CTE AS ( SELECT SalesMonth,SalesTotal , ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNumber FROM SalesList ) SELECT CASE WHEN GROUPING(RowNumber) =1 THEN 'SubTotal' ELSE SalesMonth END AS SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) HAVING GROUPING(SalesMonth) = 0 |
As seen in the result set, we add a subtotal in SQL query with help of the ROLLUP extension.
Using GROUPING SET extension as an alternative method
GROUPING SETS is another GROUP BY extension and it allows us to display multiple grouping set in one query. Such as, when we want to display monthly and quarterly sales in one result set we can use the UNION ALL statement but this would be an impractical method.
|
1 2 3 4 5 6 7 8 9 |
SELECT NULL AS SalesQuarter, SalesMonth, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY SalesMonth UNION ALL SELECT SalesQuartes, NULL AS SalesMonth, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY SalesQuartes |
GROUPING SETS extension can create the same result set with only a single query.
|
1 2 3 4 5 |
SELECT SalesQuartes,SalesMonth , SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY GROUPING SETS(SalesQuartes,SalesMonth) |
At the same time, we can use GROUPING SETS to add subtotal in SQL query. Through the following query, we can create the subtotals.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT CASE WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=0 THEN 'SubTotal' WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=1 THEN 'Grand Total' ELSE CAST(SalesYear AS varchar(10)) END AS SalesYear, SalesQuartes, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY GROUPING SETS(SalesYear,(SalesYear,SalesQuartes),()) |
Conclusion
In this article, we have learned the usage of the ROLLUP and GROUPING SETS extensions to calculate subtotals in SQL queries.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023