SQL sum 列以在每一行中显示总计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21167073/
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
sum column to show total in every row
提问by wondergoat77
I have a query that looks like this:
我有一个看起来像这样的查询：
select Group, Sum(columnA) as SumColumn
FROM table
GROUP BY Group
I get results looking like this
我得到的结果看起来像这样
+-----+---------+
|Group|SumColumn|
+-----+---------+
|A |10 |
+-----+---------+
|B |20 |
+-----+---------+
How can I change/add to this to show something like this?
我怎样才能改变/添加到这个来显示这样的东西？
+-----+---------+-----------+
|Group|SumColumn|TotalColumn|
+-----+---------+-----------+
|A |10 |30 |
+-----+---------+-----------+
|B |20 |30 |
+-----+---------+-----------+
回答by Hogan
It is hard to see what your data looks like -- but from what you posted this is what you want:
很难看出您的数据是什么样的——但从您发布的内容来看，这就是您想要的：
SELECT Name,
SumColumn,
SUM(SumColumn) AS TotalColumn
FROM
(
SELECT Group as Name, SUM(columnA) AS SumColumn
FROM Table
GROUP BY Group
) T
You might want this -- depending on other stuff.
你可能想要这个——取决于其他东西。
SELECT *,
SUM(columnA) OVER (PARTITION BY Group ORDER BY Group) AS SumColumn,
SUM(columnA) OVER (PARTITION BY Group) AS TotalColumn
FROM TABLE
回答by Tim Schmelter
回答by Gordon Linoff
You can actually mix window functions and aggregation functions in a selectstatement. So, you can do this without subqueries:
您实际上可以在select语句中混合使用窗口函数和聚合函数。因此，您可以在没有子查询的情况下执行此操作：
select Group, Sum(columnA) as SumColumn,
sum(sum(columnA)) over () as TotalColumns
FROM table
GROUP BY Group;
回答by Anup Shah
well this is odd that you want to display total column sum along with each row. Running total value is what I would expect. but if you want to then it is just a constant number and you can store the Total SUM Value in variable and then just do the Select Variable in your SLECT FROM TABLE query.
好吧，您想要与每一行一起显示总列总和，这很奇怪。运行总价值是我所期望的。但如果你想，那么它只是一个常数，你可以将总 SUM 值存储在变量中，然后在你的 SLECT FROM TABLE 查询中执行 Select Variable 。
DECLARE @table TABLE
(
c1 sysname NOT NULL
,c2 int NOT NULL
)
INSERT INTO @table( c1, c2 )
SELECT 'A',10
UNION ALL SELECT 'B',20
UNION ALL SELECT 'C',30
DECLARE @tSum BIGINT
SELECT @tSum = SUM(C2) FROM @table
SELECT c1,c2,@tSum AS Tsum
FROM @table
回答by user2989408
Though this may not be the most effective way to do it. Here is my answer.
虽然这可能不是最有效的方法。这是我的答案。
SELECT a.*,
(select Sum(columnA)
FROM table) as [TotalColumn]
FROM (select Group, Sum(columnA) as SumColumn
FROM table
GROUP BY Group) as a
回答by JanakaRao
hello this worked for me
你好这对我有用
select a.id,a.val1,b.val2,e.s
from tbl1 as a
inner join ( select c1,sum(val1) as s from tbl2 group by c1) as e
on a.c1=e.c1
回答by ire
SELECT t.Group,t.SumColumn,
(SELECT SUM(a.SumColumn)
FROM Table a
WHERE a.Group=t.Group) TotalColumn
FROM Table t
