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

score 89 · Accepted Answer · answered Jun 19 '13 at 14:39

89

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

answered Jun 19 '13 at 14:39

Taryn

242,637
56
362
405

1

@BrianKE: Can `outcomeid` ever be NULL? – gbn Jun 19 '13 at 14:48

score 6 · Answer 2 · answered Jun 19 '13 at 14:42

6

Something like this:

SELECT companyId,
  COUNT(CASE WHEN outcomeid = 36 THEN 1 END) SalesCount,
  COUNT(CASE WHEN outcomeid <> 36 THEN 1 END) NonSalesCount
FROM 
  yourtable
GROUP BY 
  companyId

should work -- COUNT() counts only not null values.

answered Jun 19 '13 at 14:42

Grzegorz Gierlik

11,112
4
47
55

score 3 · Answer 3 · answered Jun 19 '13 at 14:41

Yes. Count doesn't count NULL values, so you can do this:

select
  COUNT('x') as Everything,
  COUNT(case when OutcomeID = 36 then 'x' else NULL end) as Sales,
  COUNT(case when OutcomeID <> 36 then 'x' else NULL end) as Other
from
  YourTable

Alternatively, you can use SUM, like bluefeet demonstrated.

score 2 · Answer 4 · answered Jun 19 '13 at 14:41

SELECT
    companyId, SalesCount, TotalCount-SalesCount AS NonSalesCount
FROM
    (
    select
      companyId,
      COUNT(case when outcomeid = 36 then 1 else NULL end) SalesCount,
      COUNT(*) AS TotalCount
    from yourtable
    group by companyId
    ) X;

Using this mutually exclusive pattern with COUNT(*)

avoids a (very small) overhead of evaluating a second conditional COUNT
gives correct values if outcomeid can be NULL

Using @bluefeet's SQLFiddle with added NULLs

score 2 · Answer 5 · answered Feb 27 '21 at 20:49

Knowing COUNT() and SUM() only count non-null values and the following rule:

true or null = true
false or null = null

For fiddling around, you can take Taryn's answer and circumvent CASE altogether in a super-dirty and error-prone way!

select companyId,
  sum(outcomeid = 36 or null) SalesCount,
  sum(outcomeid <> 36 or null) NonSalesCount
from yourtable
group by companyId;

Forget to add an or null and you'll be counting everything!

SQL: Count() based on column value

5 Answers5

Linked