Two Ways To Create Totals In SQL

When presenting tabular data (i.e. data in a table), you may want to have a “total” row at the bottom.

I remember (~25!) years ago I took the approach of creating a multi-dimensional array to sum a value, row-by-row, and column-by-column. Both server-side and in JavaScript.

It’s more efficient to create a “total” row in SQL (server side). It’ll also make it easier for the front-end developer.

To start with, let’s assume we have a sample query like this:

Results:

Method 1: Creating a SQL Total with UNION ALL

The first approach I’ll mention is to basically create a copy of your dataset:

• In the copy, exclude the first column that displays the different values
• Hard-code the word “Total” to ensure that the same number of columns appear in each dataset
• Join the two datasets together via UNION ALL
• Wrap both together in a surrounding SELECT statement

Remember to remove the ORDER BY within the sub-queries.

We now have a “Total” row, but it doesn’t appear at the bottom of the dataset:

“Total” isn’t the last one there

We can fix this by updating our ORDER BY clause to include a CASE statement:

Notice the CASE statement in the ORDER BY

Explanation: Where ever the column says “Total” give it a value of 1, and all other entries are 0. Therefore, Total will always be at the end. As a secondary sort, use the other field (here, “agent”).

Now we have the Total at the end:

“Total” is correctly the last one

Method 2: Creating a SQL total with ROLLUP

This may be specific to the version of SQL you’re using. I know it works with MS SQL Server

You can simply add the function ROLLUP immediately after the GROUP BY

Be sure to include the parenthesis

This works, but you’ll see that it shows a value of NULL:

Total appears as NULL

Again, to make it easier for the front-end developer, we can resolve this by replacing NULL with “Total”.

Just doing that won’t be enough, you’ll need to use the same ‘sorting’ trick previously mentioned:

Note that I’m replacing NULL with “Total” in multiple instances

One last trick for SQL Subtotals

I often want to have my “Total” be the last row. Sometimes I’ll have a value of “Other” that I want to be second-to-last (rather than display alphabetically). Using the CASE statement in the ORDER BY clause again, we can achieve this: