SQL Sum Multiple rows in one

Question

I need help with the SUM function. I am trying to calculate the invoice amount for the same account in one total amount, but the results I get show that my SUM column only multiplies my first column by 3.

Here is what I want as the results for my layout 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, if possible, both results.

Here is what I get:

My SUM column is simply multiplied by three, it does not actually summarize the data based on the 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

Thank you for your responses. It turns out my problem is with the database problem with duplicate records, not with my logic. The quick table synchronization is fixed, and the SUM function worked as expected. This is still a useful knowledge of the SUM function, and is worth reading if you are having problems using it.

Answer 2

If you do not want to group your result, use the window function.

You did not specify 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 the SQLFiddle: http://sqlfiddle.com/#!15/2c35e/1

You can even add the current amount by adding:

 sum(bill) over (partition by account_number order by bill_date) as sum_to_date 

which will give you the amount before the current date of the line.

Answer 3

You group with BillDate , but the bill dates are different for each account, so your lines are not grouped. If you think about it, it doesn’t even make sense - they are different accounts and have different dates. The same thing applies to Bill - you are trying to summarize bills for an account, why are you grouping them?

If you leave BillDate and Bill turned off by the select and group by clauses, you will get the correct results.

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

Answer 4

You should group by the field to which you want to apply SUM, and not include in SELECT any field other than several row values, for example, COUNT, SUM, AVE, etc., because if you include the Bill field, as in in this case, only the first value in the rowset will be displayed, being almost meaningless and confusing.

This will return the invoice amount for the account number:

 SELECT SUM(Bill) FROM Table1 GROUP BY AccountNumber 

You can add additional clauses like WHERE, ORDER BY, etc. as needed.

Answer 5

I tried this, but the query will not work, indicating that my field is not valid in the select statement, because it is not contained in the aggregate function or in the GROUP BY clause. It made me leave him there. Is there any way around this?

You need to make an independent connection. You cannot aggregate and save non-aggregated data in the same subquery. For example.

 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 

Answer 6

I need the lines r1 and r2. I just want to add two lines (1074 and 649)

Heading Nr. 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