Jason Jason -4 years ago 61
SQL Question

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download