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:
We can fix this by updating our ORDER BY clause to include a CASE statement:
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:
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:
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:
