计数函数和案例

Question

数数第1-8阶段应该被计算为SAL的机会数，但我只想将第3-7阶段筛选为SQL。

尝试了case语句并在子查询中进行计数，但没有工作。代码如下：

SELECT camp, count(opp_id) as sal, 
count(opp_id (select opp_id from db.opp_data_q3 where stage not in ("01", "02")) as sql,
 from db.opp_data_q3 
where created_quarter = "Q3" 
group by camp;

预期结果：

Camp A | SAL 10 | SQL 5
Camp B | SAL 20 | SQL 3

实际结果是一个错误消息：

编译语句时出错:失败: ParseException第2:14行无法识别函数规范中“select‘'opp_id’from”附近的输入

Answer 1

我想你只是想要有条件的聚合：

select camp, count(*) as sal, 
       sum(case when stage not in ('01', '02') then 1 else 0 end) as sql
from db.opp_data_q3 
where created_quarter = 'Q3'
group by camp;