SQL 如何获取结果集最后一行中所有列值的总和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5015279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the Sum of all column values in the last row of a resultset?
提问by RealWillyWoka
I need to get the sum of all column values of a result set in the last row.
Here is my SQL query.
我需要获取最后一行中结果集的所有列值的总和。
这是我的 SQL 查询。
select Master_Code, SUM(Jan), SUM(Feb), SUM(Mar)
from dbo.foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_Code ORDER BY Master_Code ASC
something like this:
像这样：
Master_Code Jan Feb Mar
1 4 5 6
2 5 5 5
Total 9 10 11
回答by Guffa
Make a union where you repeat the same query but without the grouping:
创建一个联合，在其中重复相同的查询但没有分组：
select Title, Jan, Feb, Mar
from (
select Master_Code as Title, SUM(Jan) as Jan, SUM(Feb) as Feb, SUM(Mar) as Mar
from dbo.foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_Code ORDER BY Master_Code ASC
) x
union all
select 'Total', SUM(Jan) as Jan, SUM(Feb) as Feb, SUM(Mar) as Mar
from dbo.foobar
WHERE Participating_City = 'foofoo'
回答by JulzA
Assuming there are no null master_code rows.
假设没有空的 master_code 行。
SELECT ISNULL(Master_code, 'Total') AS Master_Code,
Jan,
Feb,
Mar
FROM (
SELECT Master_code,
SUM(Jan) AS Jan,
SUM(Feb) AS Feb,
SUM(Mar) AS Mar
FROM foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_code WITH ROLLUP
) AS DT
回答by John Bell
You can also use Coalesce and With Rollup.
您还可以使用 Coalesce 和 With Rollup。
SELECT COALESCE(Master_Code, 'TOTAL') AS MASTER_CODE, SUM(Jan), SUM(Feb), SUM(Mar)
FROM dbo.foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_Code WITH ROLLUP
ORDER BY Master_Code DESC
