Two Ways To Create Totals In SQL
Tags: webdev, analytics, sql, database
Published: 2022-06-20 00:25:16

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:
{% embed https://gist.github.com/stevesohcot/f513570e3b26a4463835020ec67d1762 %}

Results:


![Original Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dte8ul260xejqc9i0lgw.png)

## 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.

{% embed https://gist.github.com/stevesohcot/958935822af3e1fd0432984b2895d562 %}


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


![“Total” isn’t the last one there](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b7yjtnnz0o8cp5s7tveu.png)

We can fix this by updating our _ORDER BY_ clause to include a _CASE_ statement:

{% embed https://gist.github.com/stevesohcot/9616c598bc35bf38d6cb1eff549b7565 %}


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2djy2mn00wukrkbqmu9r.png)

## 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

{% embed https://gist.github.com/stevesohcot/2f74a08b15ba9c92a2640a7f11237686 %}

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

![Total appears as NULL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nhipexvss5j82gapni99.png)

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:

{% embed https://gist.github.com/stevesohcot/ee1ff01a425c2591a73ca309364a83a1 %}


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:

{% embed https://gist.github.com/stevesohcot/21692ed9818ea7ebd3182fb363ac8f75 %}

[Originally posted on Medium](https://stevesohcot.medium.com/two-ways-to-create-totals-in-sql-1f77f24067c5)