Add a summary row with totals

rollup, sql, sql-server

Solution

If you are on SQL Server 2008 or later version, you can use the `ROLLUP()` GROUP BY function:

SELECT
  Type = ISNULL(Type, 'Total'),
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

This assumes that the `Type` column cannot have NULLs and so the NULL in this query would indicate the rollup row, the one with the grand total. However, if the `Type` column can have NULLs of its own, the more proper type of accounting for the total row would be like in @Declan_K's answer, i.e. using the `GROUPING()` function:

SELECT
  Type = CASE GROUPING(Type) WHEN 1 THEN 'Total' ELSE Type END,
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

Problem

I know this sounds crazy and probably should not be done this way but I need something like this - I have a records from `SELECT [Type], [Total Sales] From Before` I want to add an extra row at the end to show the SUM at the end of the table (After). Could this be done?

Original source