SELECT col1, col2,
SUM(CASE WHEN category='CAT1' THEN val END) as SUMvalCAT1,
SUM(CASE WHEN category='CAT2' THEN val END) as SUMvalCAT2
from TABLENAME
GROUP BY col1, col2;
这是一个工作查询,我在col1,col2
组中为两个不同的category
求和val
。
现在,同样地,我想要另一列(例如XYZ
)的不同值的计数。所以如果我输入:
SELECT col1, col2,
SUM(CASE WHEN category='CAT1' THEN val END) as SUMvalCAT1,
SUM(CASE WHEN category='CAT2' THEN val END) as SUMvalCAT2,
COUNT(CASE WHEN category='CAT1' THEN DISTINCT XYZ END) as CAT1dist,
COUNT(CASE WHEN category='CAT2' THEN DISTINCT XYZ END) as CAT2dist
from TABLENAME
GROUP BY col1, col2;
但是这会抛出一个错误。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT XYZ END) as CAT1dist,
COUNT(CASE WHEN category='CAT2' THEN DISTI' at line 4
可能出了什么问题?
DISTINCT必须出现在COUNT
参数的开头:
COUNT(DISTINCT CASE ... END)
这是COUNT
函数的特殊修饰符,不能任意应用于值
您需要将DISTINCT
关键字放在CASE
之前,如下所示:
SELECT col1, col2,
SUM(CASE WHEN category='CAT1' THEN val END) as SUMvalCAT1,
SUM(CASE WHEN category='CAT2' THEN val END) as SUMvalCAT2,
COUNT(DISTINCT CASE WHEN category='CAT1' THEN XYZ END) as CAT1dist,
COUNT(DISTINCT CASE WHEN category='CAT2' THEN XYZ END) as CAT2dist
from TABLENAME
GROUP BY col1, col2;
SQLFiddle