sqlite - GROUP BY 后求和

Question

这是我的表

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

我想做的是，首先:分组发票，然后将分组发票的付款求和(300+200)

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

预期结果是 500。但是使用上面的代码我得到 800。如何正确地做到这一点？

Answer 1

如果您想排除重复项，请使用DISTINCT:

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

对于customerID = 1，您可以这样做

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

无需GROUP BY。