  # How to calculate Subtotals in SQL Queries

July 30, 2021

## 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. ## 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: 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. 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. ## 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. 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. 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. 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. In this result set, we need to remove the RowNumber column and only show the SalesMonth column and its’ subtotal rows. 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. 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. GROUPING SETS extension can create the same result set with only a single query. At the same time, we can use GROUPING SETS to add subtotal in SQL query. Through the following query, we can create the subtotals. ## Conclusion

In this article, we have learned the usage of the ROLLUP and GROUPING SETS extensions to calculate subtotals in SQL queries.

Latest posts by Esat Erkec (see all)
Development, SQL commands, T-SQL 