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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Sum Multiple rows into one

Examples related to sql

Examples related to function

Examples related to group-by

Examples related to sum