SQL计数(按项目分组)

Question

我有查询

select count(t1.contactid) [Count],
case
when f.item like '%yes%' then 'Yes'
when f.item like '%Correct%' then 'Yes'  
when f.item like '%None%' then 'No'
when f.item like '% Notask%' then 'No'
end as Items
from table1 t1
join table2 t2 on t1.contactid =  t2.contactid

group by f.item

如何按每个项目进行分组

the result
Count       items
10           No
20           No
1           Yes
1           Yes
3           Yes
1           Yes

我的目标是分组"否"one_answers"是"，所以看起来应该像

Count     Items
30          No
6           Yes 

谢谢

Answer 1

您可以在group by中重复case表达式。或者使用子查询或CTE。但我喜欢apply：

select v.items, count(t1.contactid) as [Count]
from table1 t1 join
table2 t2
on t1.contactid =  t2.contactid cross apply
(values (case when f.item like '%yes%' then 'Yes'
when f.item like '%Correct%' then 'Yes'  
when f.item like '%None%' then 'No'
when f.item like '% Notask%' then 'No'
end)
) v(Items)
group by v.items

Answer 2

select v.items, count(t1.contactid) as [Count]
from table1 t1 join
table2 t2
on t1.contactid =  t2.contactid cross apply
(values (case when f.item like '%yes%' then 'Yes'
when f.item like '%Correct%' then 'Yes'  
when f.item like '%None%' then 'No'
when f.item like '% Notask%' then 'No'
end)
) v(Items)
group by v.items