Add a summary row with totals
Solution 1
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)
;
Solution 2
This is the more powerful grouping / rollup syntax you'll want to use in SQL Server 2008+. Always useful to specify the version you're using so we don't have to guess.
SELECT
[Type] = COALESCE([Type], 'Total'),
[Total Sales] = SUM([Total Sales])
FROM dbo.Before
GROUP BY GROUPING SETS(([Type]),());
Craig Freedman wrote a great blog post introducing GROUPING SETS
.
Solution 3
Try to use union all
as below
SELECT [Type], [Total Sales] From Before
union all
SELECT 'Total', Sum([Total Sales]) From Before
if you have problem with ordering, as i-one suggested try this:
select [Type], [Total Sales]
from (SELECT [Type], [Total Sales], 0 [Key]
From Before
union all
SELECT 'Total', Sum([Total Sales]), 1 From Before) sq
order by [Key], Type
Solution 4
You could use the ROLLUP operator
SELECT CASE
WHEN (GROUPING([Type]) = 1) THEN 'Total'
ELSE [Type] END AS [TYPE]
,SUM([Total Sales]) as Total_Sales
From Before
GROUP BY
[Type] WITH ROLLUP
Solution 5
If you want to display more column values without an aggregation function use GROUPING SETS
instead of ROLLUP
:
SELECT
Type = ISNULL(Type, 'Total'),
SomeIntColumn = ISNULL(SomeIntColumn, 0),
TotalSales = SUM(TotalSales)
FROM atable
GROUP BY GROUPING SETS ((Type, SomeIntColumn ), ())
ORDER BY SomeIntColumn --Displays summary row as the first row in query result
user2103670
Updated on July 05, 2022Comments
-
user2103670 almost 2 years
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?
-
Sparky almost 11 yearsMinor note here, if values of Type are alphabetical after the word (U,V,W,etc. ) Total, then Total line might not appear on the bottom row...
-
i-one almost 11 yearsI agree with @Sparky, this way it should be e.g.
select [Type], [Total Sales] from (SELECT [Type], [Total Sales], 0 [Key] From Before union all SELECT 'Total', Sum([Total Sales]), 1 From Before) sq order by [Key], Type
-
Sparky almost 11 yearsWithout knowing the possible values of [Type], Cast() to VarChar(5) won't really address it. i-one shows the right solution, added a numeric counter to force the sort order...
-
Adir D almost 11 yearsAnother note: the UNION ALL variants of this will require two scans instead of one.
-
Sohel Pathan over 3 yearsI liked this solution. If you want to query and retrieve results from more than two columns then this is the good solution.
-
Allen about 3 yearsUpdated link to Craig Freedman's blog post: docs.microsoft.com/en-us/archive/blogs/craigfr/…
-
Adir D about 3 years@Allen Thanks, wouldn't it be great if Microsoft could figure out how to move content without breaking existing links