mysql - 如何对按列分组的表中的行进行计数和求和？

Question

我有三个表:

• 产品(id、名称、category_id、...)
• 类别(id，...)
• 销售额(id_product、id_user、已售出、date_sale、数量)

---

我正在尝试获得销量最高的产品:

SELECT P.`id`, P.`name`, C.`name` as catName, SUM(S.`quantity`) 
FROM `products` AS P, `categories` AS C, `sales` AS S 
WHERE P.`category_id` = C.`id` 
    AND P.`id` = S.`id_product` 
    AND P.`id` IN ( 
                    SELECT `id_product` FROM `sales` 
                    WHERE `selled`=1
                  ) 
LIMIT 8

提供我想要的所有信息，除了数量未按 id_product 分组/求和。

---

因此，为了解决我正在尝试 SUM(S.quantity) 和 按 id_product 分组的问题:

SELECT P.`id`, P.`name`, C.`name` as catName, SUM(S.`quantity`) 
FROM `products` AS P, `categories` AS C, `sales` AS S 
WHERE P.`category_id` = C.`id` 
    AND P.`id` = S.`id_product` 
    AND P.`id` IN ( 
                    SELECT `id_product` FROM `sales` 
                    WHERE `selled`=1
                  ) 
LIMIT 8 
GROUP BY P.`id`

查询后，我收到一个错误

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY P.id' at line 3

有没有办法实现我想要的？为什么group by不能这样用？

Answer 1

GROUP BY 在 LIMIT 之前

节日快乐!