如何同时使用where-between和group-by

l7mqbcuq 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(755)

如何在同一查询中使用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错误，所以我尝试和在哪里的地方，但这也不是分组结果正确，它只给出了两个相同的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'

我错过了什么？

sql mysql Aggregate

来源：https://stackoverflow.com/questions/53045396/how-to-use-where-between-and-group-by-at-same-time

3条答案

按热度按时间

8aqjt8rx1#

这些关键字的书写顺序如下： SELECT , FROM , JOIN , WHERE , GROUP BY , HAVING , ORDER 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
WHERE date-s BETWEEN '2018-09-01' AND '2018-10-01'
GROUP BY sales-store-id;

赞(0）回复(0）举报 2021-06-18

fzsnzjdm2#

我们不能在groupby之后使用where子句。使用此查询

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`

哪里 date-s 在“2018-09-01”和“2018-10-01”之间 sales-store-id

赞(0）回复(0）举报 2021-06-18

voase2hg3#

这个 WHERE 子句应出现在 GROUP BY sql语句中的子句。
sql语言定义的子句顺序为：
选择
从
参加
哪里
分组依据
有
订货人
限制（mysql扩展）