SQL Sum Multiple rows into one

Question

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 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:

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.

Answer 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?

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