mysql - 如何同时使用WHERE BETWEEN和GROUP BY

Question

如何在同一查询中使用where between 和group by

  SELECT `sales-store-id`,
  (SUM (`gen-acute-sales-after-returns`) + SUM(`gen- chronic-sales-after-returns`)) 
   AS `totalGenericSales` ,
  (SUM (`eth-acute-sales- after-returns`) + SUM(`eth-chronic-sales-after-returns`) + SUM(`others-sales- 
after-returns`))
 AS `totalEthicalSales` 
 FROM `sales` GROUP BY `sales-store-id` 
WHERE `date-s` BETWEEN '2018-09-01' AND '2018-10-01'

这会导致 SQL 错误，所以我尝试用 AND 代替 WHERE 但这也没有正确地对结果进行分组，它只给出了 2 个具有相同 ID 的结果

   SELECT `sales-store-id`,
      (SUM (`gen-acute-sales-after-returns`) + SUM(`gen- chronic-sales-after-returns`)) 
       AS `totalGenericSales` ,
      (SUM (`eth-acute-sales- after-returns`) + SUM(`eth-chronic-sales-after-returns`) + SUM(`others-sales- 
    after-returns`))
     AS `totalEthicalSales` 
     FROM `sales` GROUP BY `sales-store-id` 
    AND `date-s` BETWEEN '2018-09-01' AND '2018-10-01'

我在这里错过了什么？

Answer 1

WHERE 子句应该出现在 SQL 语句中的 GROUP BY 子句之前。

SQL 语言定义的子句顺序是:

• 选择
• 来自
• 加入
• 哪里
• 分组依据
• 拥有
• 订购方式
• 限制(MySQL 扩展)