mysql - 正确使用分组依据功能

我正在运行一个查询，以找出我的数据库中移动缓慢的股票，但它给出了一个错误，说无效使用按条件分组。其逻辑是，我想找出过去120天的总销售额不到该项目的总库存的5%的项目。
以下是查询：-

SELECT
  itemmaster.item as item,
  stockbalance.qty as total_stock,
  SUM(saleitems.saleqty) as total_sales
FROM itemmaster
JOIN stockbalance ON stockbalance.item = itemmaster.item
JOIN saleitems ON saleitems.item = itemmaster.item
WHERE saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW()
  AND (SUM(saleitems.saleqty)) < (stockbalance.qty * 0.05)

最佳答案

您需要将sum条件从WHERE子句移动到HAVING子句。请尝试以下操作：

SELECT
  itemmaster.item as item,
  SUM(stockbalance.qty) as total_stock,
  SUM(saleitems.saleqty) as total_sales
FROM itemmaster
JOIN stockbalance ON stockbalance.item = itemmaster.item
JOIN saleitems ON saleitems.item = itemmaster.item
WHERE
    saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW()
GROUP BY
    itemmaster.item
HAVING
    total_sales < (total_stock * 0.05)