Esat Erkec
How to use ROLLUP extension in T-SQL

How to calculate Subtotals in SQL Queries

July 30, 2021 by

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.

Subtotal in a receipt

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.

Create an example table in SQL Server

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:

Calculating and adding a grandtotal into a query

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.

How to use ROLLUP extension in T-SQL

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.

Using ROLLUP extension for multiple columns

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.

Using GROUPING  function in T-SQL

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.

How to use the GROUPING  function  in a query

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.

How to calculate a subtotal for one column

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.

Filtering final total in a query

In this result set, we need to remove the RowNumber column and only show the SalesMonth column and its’ subtotal rows.

ROLLUP extension with subtotal

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.

Calculating a subtotal for one column

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.

How to use GROUPING SET extension in T-SQL

GROUPING SETS extension can create the same result set with only a single query.

Using GROUPING SET extension in T-SQL

At the same time, we can use GROUPING SETS to add subtotal in SQL query. Through the following query, we can create the subtotals.

Using GROUPING SET and ROLLUP similarties

Conclusion

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

Esat Erkec
Latest posts by Esat Erkec (see all)
5,498 Views