CODEWITHSUNDEEP
sqlfunctiongroup-bysum
Jason
Jason

Reputation: 247

SQL Sum Multiple rows into one

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.

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. 

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

Upvotes: 18

Views: 187254

Answers (5)

Jason
Jason

Reputation: 247

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.

Upvotes: 0

user330315
user330315

Reputation:

If you don't want to group your result, use a window function.

You didn't state your DBMS, but this is 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

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.

Upvotes: 25

ubanerjea
ubanerjea

Reputation: 474

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?

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

Upvotes: 1

Dave Zych
Dave Zych

Reputation: 21887

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?

If you leave BillDate and Bill off of the select and group by clauses you'll get the correct results.

SELECT AccountNumber, SUM(Bill)
FROM Table1
GROUP BY AccountNumber

Upvotes: 13

jvicab
jvicab

Reputation: 286

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.

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.

Upvotes: 3

Related Questions