Add a summary row with totals

by

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

More Related Contents:

  1. Row Offset in SQL Server
  2. Reset identity seed after deleting records in SQL Server
  3. SQL Server recursive self join
  4. Generate a resultset of incrementing dates in TSQL
  5. How to simulate UNPIVOT in Access?
  6. Real life example, when to use OUTER / CROSS APPLY in SQL
  7. How to get script of SQL Server data? [duplicate]
  8. Better techniques for trimming leading zeros in SQL Server?
  9. Convert integer to hex and hex to integer
  10. Query error with ambiguous column name in SQL
  11. Why (and how) to split column using master..spt_values?
  12. How would you implement sequences in Microsoft SQL Server?
  13. Splitting delimited values in a SQL column into multiple rows
  14. SQL: parse the first, middle and last name from a fullname field
  15. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  16. SSIS Package not wanting to fetch metadata of temporary table
  17. How to store historical records in a history table in SQL Server
  18. SQL GROUP BY CASE statement with aggregate function
  19. separate comma separated values and store in table in sql server
  20. Get the last day of the month in SQL
  21. INSERT VALUES WHERE NOT EXISTS
  22. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  23. Inner Joining three tables
  24. SQL take just the numeric values from a varchar
  25. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  26. How to subtract 30 days from the current date using SQL Server
  27. Add a row number to result set of a SQL query
  28. Use one CTE many times
  29. How to Select Every Row Where Column Value is NOT Distinct
  30. How to do a case sensitive GROUP BY?

Leave a Comment