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:

	SELECT agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY agent
	ORDER BY agent

view raw sql-subtotals-original-query.sql hosted with ❤ by GitHub

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.

	SELECT
	agent, SUM(totalHours) AS totalHours
	FROM (
	-- Original query
	SELECT agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY agent
	UNION ALL
	-- Copy, but don't show (or GROUP BY) the first column
	-- instead, hard-code the word "Total"
	SELECT 'Total', SUM(hours) AS totalHours
	FROM myTable
	) AS qryMain
	GROUP BY
	agent
	ORDER BY
	agent

view raw sql-subtotals-union-all-no-order.sql hosted with ❤ by GitHub

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:

	SELECT
	agent, SUM(totalHours) AS totalHours
	FROM (
	-- Original query
	SELECT agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY agent
	UNION ALL
	-- Copy, but don't show (or GROUP BY) the first column
	-- instead, hard-code the word "Total"
	SELECT 'Total', SUM(hours) AS totalHours
	FROM myTable
	) AS qryMain
	GROUP BY
	agent
	ORDER BY
	CASE
	WHEN agent = 'Total' THEN 1
	ELSE 0
	END,
	agent

view raw sql-subtotals-union-all-with-order.sql hosted with ❤ by GitHub

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

	SELECT agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY
	ROLLUP (agent)

view raw sql-subtotals-roll-up-no-order.sql hosted with ❤ by GitHub

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:

	SELECT ISNULL(agent,'Total') AS agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY
	ROLLUP (agent)
	ORDER BY
	CASE
	WHEN ISNULL(agent, 'Total') = 'Total' THEN 1
	ELSE 0
	END,
	ISNULL(agent,'Total')

view raw sql-roll-up-with-order.sql hosted with ❤ by GitHub

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:

	SELECT ISNULL(agent,'Total') AS agent, SUM(hours) AS totalHours
	FROM myTable
	GROUP BY
	ROLLUP (agent)
	ORDER BY
	CASE
	WHEN ISNULL(agent, 'Total') = 'Other' THEN 1
	WHEN ISNULL(agent, 'Total') = 'Total' THEN 9
	ELSE 0
	END,
	ISNULL(agent,'Total')

view raw sql-roll-up-with-order-incl-other.sql hosted with ❤ by GitHub

Originally posted on Medium