SQL: Count() based on column value

Question

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)?

Answer 1

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