SQL Sum 多行合二为一
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21243589/
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
SQL Sum Multiple rows into one
提问by Jason
I need some help with the SUM feature. I am trying to SUM the bill amounts for the same account into one grand total, but the results I am getting show my SUM column just multiples my first column by 3.
我需要有关 SUM 功能的一些帮助。我试图将同一帐户的账单金额汇总为一个总计，但我得到的结果显示我的 SUM 列只是将我的第一列乘以 3。
Here is what I want as results for my mock data:
这是我想要的模拟数据结果：
AccountNumber Bill BillDate
1 100.00 1/1/2013
1 150.00 2/1/2013
1 200.00 3/1/2013
2 75.00 1/1/2013
2 100.00 2/1/2013
Query:
询问：
SELECT AccountNumber, Bill, BillDate, SUM(Bill)
FROM Table1
GROUP BY AccountNumber, Bill, BillDate
AccountNumber Bill BillDate SUM(Bill)
1 100.00 1/1/2013 450.00
1 150.00 2/1/2013 450.00
1 200.00 3/1/2013 450.00
2 75.00 1/1/2013 175.00
2 100.00 2/1/2013 175.00
OR
或者
AccountNumber Bill SUM(Bill)
1 100.00 450.00
2 75.00 175.00
I would prefer to have both results if possible.
如果可能，我更愿意同时获得这两个结果。
Here is what I am getting:
这是我得到的：
My SUM column is just multiplying by three, it's not actually summing the data based on account Number.
我的 SUM 列只是乘以 3，它实际上并不是根据帐号对数据进行求和。
AccountNumber Bill BillDate SUM(Bill)
1 100.00 1/1/2013 300.00
1 150.00 2/1/2013 450.00
1 200.00 3/1/2013 600.00
2 75.00 1/1/2013 225.00
2 100.00 2/1/2013 300.00
采纳答案by Jason
Thank you for your responses. Turns out my problem was a database issue with duplicate entries, not with my logic. A quick table sync fixed that and the SUM feature worked as expected. This is all still useful knowledge for the SUM feature and is worth reading if you are having trouble using it.
谢谢你的回复。原来我的问题是重复条目的数据库问题，而不是我的逻辑。快速表同步解决了这个问题，SUM 功能按预期工作。这些仍然是 SUM 功能的有用知识，如果您在使用它时遇到问题，则值得一读。
回答by a_horse_with_no_name
If you don't want to group your result, use a window function.
如果您不想对结果进行分组，请使用窗口函数。
You didn't state your DBMS, but this is ANSI SQL:
您没有说明您的 DBMS，但这是 ANSI SQL：
SELECT AccountNumber,
Bill,
BillDate,
SUM(Bill) over (partition by accountNumber) as account_total
FROM Table1
order by AccountNumber, BillDate;
Here is an SQLFiddle: http://sqlfiddle.com/#!15/2c35e/1
这是一个 SQLFiddle：http://sqlfiddle.com/#!15/2c35e/1
You can even add a running sum, by adding:
您甚至可以通过添加以下内容来添加运行总和：
sum(bill) over (partition by account_number order by bill_date) as sum_to_date
which will give you the total up to the current's row date.
这将为您提供截至当前行日期的总数。
回答by Dave Zych
You're grouping with BillDate, but the bill dates are different for each account so your rows are not being grouped. If you think about it, that doesn't even make sense - they are different bills, and have different dates. The same goes for the Bill- you're attempting to sum bills for an account, why would you group by that?
您使用 分组BillDate，但每个帐户的账单日期不同，因此您的行未分组。如果你仔细想想，这甚至没有意义——它们是不同的账单，并且有不同的日期。这同样适用于Bill- 您正在尝试为一个帐户汇总账单，为什么要按此分组？
If you leave BillDateand Billoff of the select and group by clauses you'll get the correct results.
如果您离开BillDate和Bill关闭 select 和 group by 子句，您将获得正确的结果。
SELECT AccountNumber, SUM(Bill)
FROM Table1
GROUP BY AccountNumber
回答by jvicab
You should group by the field you want the SUM apply to, and not include in SELECT any field other than multiple rows values, like COUNT, SUM, AVE, etc, because if you include Bill field like in this case, only the first value in the set of rows will be displayed, being almost meaningless and confusing.
您应该按您希望 SUM 应用到的字段进行分组，并且不要在 SELECT 中包含除多行值以外的任何字段，例如 COUNT、SUM、AVE 等，因为如果在这种情况下包含 Bill 字段，则只有第一个值在行集合中将被显示，几乎毫无意义且令人困惑。
This will return the sum of bills per account number:
这将返回每个帐号的账单总和：
SELECT SUM(Bill) FROM Table1 GROUP BY AccountNumber
You could add more clauses like WHERE, ORDER BY etc as needed.
您可以根据需要添加更多子句，如 WHERE、ORDER BY 等。
回答by ubanerjea
I tried this, but the query won't run telling me my field is invalid in the select statement because it is not contained in either an aggregate function or the GROUP BY clause. It's forcing me to keep it there. Is there a way around this?
我试过这个，但查询不会运行，告诉我我的字段在 select 语句中无效，因为它不包含在聚合函数或 GROUP BY 子句中。它迫使我把它留在那里。有没有解决的办法？
You need to do a self-join. You can't both aggregate and preserve non-aggregated data in the same subquery. E.g.
您需要进行自联接。您不能在同一个子查询中同时聚合和保留非聚合数据。例如
select q2.AccountNumber, q2.Bill, q2.BillDate, q1.BillSum
from
(
SELECT AccountNumber, SUM(Bill) as BillSum
FROM Table1
GROUP BY AccountNumber
) q1,
(
select AccountNumber, Bill, BillDate
from table1
) q2
where q1.AccountNumber = q2.AccountNumber
