SQL:基于列值的 Count() [英] SQL: Count() based on column value

问题描述

我有一个表格如下:

CallID   | CompanyID  | OutcomeID
----------------------------------
1234     | 3344       | 36
1235     | 3344       | 36
1236     | 3344       | 36
1237     | 3344       | 37
1238     | 3344       | 39
1239     | 6677       | 37
1240     | 6677       | 37

我想创建一个 SQL 脚本来计算销售结果的数量和所有其他尝试的数量(任何小于 36 的次数)，例如:

I would like to create a SQL script that counts the number of Sales outcomes and the number of all the other attempts (anything <> 36), something like:

CompanyID  | SalesCount  | NonSalesCount
------------------------------------------
3344       | 3           | 1
6677       | 0           | 2

有没有办法做一个包含像 COUNT(CallID WHERE OutcomeID = 36) 这样的条件的 COUNT()?

Is there a way to do a COUNT() that contains a condition like COUNT(CallID WHERE OutcomeID = 36)?

推荐答案

您可以将 CASE 表达式与聚合一起使用，以根据 outcomeId 值获得总数:

You can use a CASE expression with your aggregate to get a total based on the outcomeId value:

select companyId,
  sum(case when outcomeid = 36 then 1 else 0 end) SalesCount,
  sum(case when outcomeid <> 36 then 1 else 0 end) NonSalesCount
from yourtable
group by companyId;

参见SQL Fiddle with Demo

这篇关于SQL:基于列值的 Count()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！