Search code examples
sqlsql-serverrollup

Add a summary row with totals

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?

enter image description here

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)
;