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

问题描述

我有一个表如下：

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

我想创建一个SQL脚本来计算销售结果数量和所有其他尝试（任何<> 36），例如：

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

是否有一种方法来做一个包含一个条件的COUNT（）COUNT（CallID WHERE OutcomeID = 36）？

解决方案

您可以使用CASE表达式与您的聚合以基于 outcomeId value：

  select companyId，
 sum（case if outcomeid = 36 then 1 else 0 end）SalesCount，
 sum（case if resultid<> 36 then 1 else 0 end）NonSalesCount 
从你的表
 group by companyId;    > SQL Fiddle with Demo  
 
I have a table as follows:
CallID   | CompanyID  | OutcomeID
----------------------------------
1234     | 3344       | 36
1235     | 3344       | 36
1236     | 3344       | 36
1237     | 3344       | 37
1238     | 3344       | 39
1239     | 6677       | 37
1240     | 6677       | 37
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
Is there a way to do a COUNT() that contains a condition like COUNT(CallID WHERE OutcomeID = 36)?
解决方案
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;
See SQL Fiddle with Demo

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