CODEWITHSUNDEEP
sqlite
Zils
Zils

Reputation: 405

SUM after GROUP BY

Here is mytable

   customerID  invoice     payment
   1            101            300
   1            101            300
   3            102            200
   4            103            200
   1            104            200

What I want to do is, first: group invoice, then sum the payment of grouped invoice (300+200) 

 select customerID  , sum(payment) as totalpaid from mytable WHERE
 customerID  ='1' group by `invoice`

Expected result is 500. But I'm getting 800 using above code. How to do that correctly?

Upvotes: 0

Views: 434

Answers (1)

forpas
forpas

Reputation: 164099

If you want to exclude duplicates use DISTINCT:

SELECT 
  customerID, 
  SUM(payment) as totalpaid 
FROM 
   (SELECT DISTINCT customerID, invoice, payment FROM customers) 
GROUP BY customerID

For customerID = 1 you can do

SELECT 
  customerID, 
  SUM(payment) as totalpaid 
FROM 
   (SELECT DISTINCT customerID, invoice, payment FROM customers) 
WHERE customerID = 1

no need to GROUP BY.

Upvotes: 2

Related Questions