温度字段INT(11)在CASE语句中未求和:
SELECT storenumber as storenumber,
SUM((CASE temperature WHEN temperature < 0 THEN SumOfTotalPrice ELSE 0 END)) AS Under0,
SUM((CASE temperature WHEN temperature BETWEEN 0 AND 20 THEN SumOfTotalPrice ELSE 0 END)) AS `0To20`,
SUM((CASE temperature WHEN temperature BETWEEN 21 AND 30 THEN SumOfTotalPrice ELSE 0 END)) AS `21To30`,
SUM((CASE temperature WHEN temperature BETWEEN 31 AND 40 THEN SumOfTotalPrice ELSE 0 END)) AS `31To40`,
SUM((CASE temperature WHEN temperature BETWEEN 41 AND 50 THEN SumOfTotalPrice ELSE 0 END)) AS `41To50`,
SUM((CASE temperature WHEN temperature BETWEEN 51 AND 60 THEN SumOfTotalPrice ELSE 0 END)) AS `51To60`,
SUM((CASE temperature WHEN temperature BETWEEN 61 AND 70 THEN SumOfTotalPrice ELSE 0 END)) AS `61To70`,
SUM((CASE temperature WHEN temperature BETWEEN 71 AND 80 THEN SumOfTotalPrice ELSE 0 END)) AS `71To80`,
SUM((CASE temperature WHEN temperature BETWEEN 81 AND 90 THEN SumOfTotalPrice ELSE 0 END)) AS `81To90`,
SUM((CASE temperature WHEN temperature > 90 THEN SumOfTotalPrice ELSE 0 END)) AS `Over90`
FROM `reconciled`.totalsalesbystoreandtemperature
GROUP BY storenumber
这是查询的结果:
这是基础记录集的内容:
最佳答案
您需要将所有CASE temperature WHEN ...
部分更改为CASE WHEN ...
,如下所示:
CASE temperature WHEN temperature BETWEEN 31 AND 40 THEN
改成
CASE WHEN temperature BETWEEN 31 AND 40 THEN
当前,
CASE
尝试将temperature
列与WHEN
语句中给出的逻辑测试结果进行比较。由于这些类型不同,因此您会不断得到false
和0
的结果。