SQL - 如何统计一列中多个值的出现次数

Question

我有一张表格，其中有一个名为StatusID的列，该列有许多不同的可能值，我想要做的是生成以下格式的报告，该报告能够产生各种条件的计数。

期望输出：

Notes | Total | Valid | Invalid | Consults Booked |

总数是指返回的所有行数 - 在以下查询中已经有了

有效是指任何StatusID不为5、7或42

无效是指5、7和42的数量

预约咨询是指4的数量

(无效 + 有效应等于总数)

到目前为止，我只能得到Total，我不知道如何使用IF或其他任何东西确定其他值。

目前的查询

select notes, tLeadStatus.Status, tLeadStatus.StatusID,
       count(*) as Total from LeadManagement.dbo.tLead with (NOLOCK)
left join LeadManagement.dbo.tInternetLead on tLead.Leadid = tinternetlead.leadid
left join LeadManagement..tLeadStatus on tLeadStatus.StatusID = tLead.Status
where (CampaignID = '12327')
  and (registerdate >= '2013-03-01' and registerdate < '2013-04-01')
group by notes,tLeadStatus.StatusID,tLeadStatus.Status

- martincarlin87

Answer 1

SUM(CASE WHEN StatusID NOT IN (5, 7, 42) THEN 1 ELSE 0 END) AS Valid,
SUM(CASE WHEN StatusID IN (5, 7, 42) THEN 1 ELSE 0 END) AS Invalid,
SUM(CASE WHEN StatusId = 4 THEN 1 ELSE 0 END) AS 'Consults Booked'

- Aushin

---

啊，没想到可以这样做...非常感谢你的帮助！ - martincarlin87

Answer 2

您可以使用带有CASE的聚合函数来获取其他列：

select notes, 
  count(*) as Total,
  sum(case when tLeadStatus.StatusID not in (5, 7, 42) then 1 else 0 end) Valid,
  sum(case when tLeadStatus.StatusID  in (5, 7, 42) then 1 else 0 end) Invalid,
  sum(case when tLeadStatus.StatusID= 4 then 1 else 0 end) ConsultsBooked
from LeadManagement.dbo.tLead with (NOLOCK)
left join LeadManagement.dbo.tInternetLead 
  on   tLead.Leadid = tinternetlead.leadid
left join LeadManagement..tLeadStatus 
  on tLeadStatus.StatusID = tLead.Status
where (CampaignID = '12327')
  and (registerdate >= '2013-03-01' and registerdate < '2013-04-01')
group by notes

- Taryn