按组依据对多列求和

Question

我的数据库中有这些数据

Purchase ID: 0012 <br>
Product: 1 <br>
Delivered: 100
Pruchase ID: 0013<br>
Product: 1<br>
Delivered: 10

当我对相同的产品求和时，它应该是110，但当我使用此查询时，总和将是440

SELECT `purchase_orders`.`product`, SUM(purchase_orders.delivered) as system_count, `products`.`code`
FROM `purchase_orders`
JOIN `products` ON `products`.`id` = `purchase_orders`.`product`
WHERE `purchase_orders`.`delivery_status` = 4
GROUP BY `purchase_orders`.`product`

谁能帮我我的 SQl 有什么问题？

Answer 1

你也应该GROUP BYproducts.code。

像下面一样

SELECT `purchase_orders`.`product`, SUM(purchase_orders.delivered) as system_count, `products`.`code`
FROM `purchase_orders`
JOIN `products` ON `products`.`id` = `purchase_orders`.`product`
WHERE `purchase_orders`.`delivery_status` = 4
GROUP BY `purchase_orders`.`product`, `products`.`code`

Answer 2

select po.Product, sum(po.Delivered) as count from purchase_orders po
group by po.Product