SQL Sum将多行合并为一行

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

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

6 个解决方案

#1

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.

这将为您提供当前行日期的总计。

#2

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进行分组,但每个帐户的帐单日期不同,因此您的行不会被分组。如果你考虑一下,这甚至没有意义 - 它们是不同的账单,并且有不同的日期。这条例草案也是如此 - 你试图为一个帐户汇总账单,为什么要按此分组呢?

If you leave BillDate and Bill off 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

#3

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等。

#4

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

#5

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功能来说仍然是有用的知识,如果您在使用它时遇到问题,则值得一读。

#6

i'm having to rows r1 and r2 . I just want to add two rows(1074 and 649)

我需要行r1和r2。我只想添加两行(1074和649)

No.of Titles 1074 649

标题号1074 649

Select  count(distinct title) as 'No.of Titles'
FROM `wp_weblib_collection` inner join wp_terms t on (category = t.term_id)
where str_to_date(recv_date, '%d/%m/%Y') 
between str_to_date('01/09/2000', '%d/%m/%Y') and str_to_date('01/09/2015', '%d/%m/%Y') 
and t.term_id IN ('47','48') group by category

#1