设置具有多个日期范围的多个和

Question

这是我试图完成的查询。

SELECT salesrep, 
SUM(lines_total) AS OTSUM,
SUM(order_type = 'NEW') OTNEW,
SUM(order_type = 'REPEAT') OTREPEAT,
SUM(lines_total 
WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
SUM(lines_total 
WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'

当然，这是失败的，但我如何为多个日期范围设置SUMS，末尾的WHERE日期范围需要保持原样。

对于这些线路：

SUM(lines_total WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
SUM(lines_total WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM

Answer 1

条件聚合需要CASE表达式，如：

SUM(CASE WHEN order_closed BETWEEN '2020-12-01' AND '2020-12-31' THEN lines_total ELSE 0 END) AS OTTMSUM

对于每个和，相应地更改BETWEEN '2020-12-01' AND '2020-12-31'。

Answer 2

要让salesrep将数据按行对齐，您仍然需要添加所有数字，但可以在超出范围的地方用零代替。。。。

SUM(IF (order_closed BETWEEN '2020-11-01' AND '2020-11-30', lines_total, 0)) AS OTLMSUM

你指出你得到了一个语法错误，但这看起来像是一个语义错误：

SUM(order_type = 'NEW') OTNEW

你真的想把这些加起来吗？数数？把它们分类？

查询可能类似于。。。

SELECT salesrep, 
SUM(lines_total) AS OTSUM,
SUM(IF(order_type = 'NEW', 1, 0)) OTNEW,
SUM(IF(order_type = 'REPEAT', 1, 0)) OTREPEAT,
SUM(IF (order_closed BETWEEN '2020-11-01' AND '2020-11-30', lines_total, 0)) AS OTLMSUM
SUM(if(order_closed BETWEEN '2020-12-01' AND '2020-12-31', lines_total, 0)) AS OTTMSUM
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'

此外，对于聚合查询(SUM、AVERAGE、COUNT…(，您应该有一个GROUP BY子句。。。。

GROUP BY salesrep 

然而，这样做不是更简单吗。。。。

SELECT salesrep, 
SUM(lines_total) AS OTSUM,
SUM(IF(order_type = 'NEW', 1, 0)) AS OTNEW,
SUM(IF(order_type = 'REPEAT', 1, 0)) AS OTREPEAT,
DATE_FORMAT(order_closed, '%Y-%m') AS month,
SUM(lines_total) AS otsum
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY salesrep, DATE_FORMAT(order_closed, '%Y-%m')