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

Taryn · Accepted Answer · 2013-06-19 14:39:49Z

95

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, 2013 at 14:39

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gbn Over a year ago

@BrianKE: Can outcomeid ever be NULL?

Grzegorz Gierlik · Accepted Answer · 2013-06-19 14:42:27Z

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, 2013 at 14:42

Grzegorz Gierlik

11.3k4 gold badges49 silver badges57 bronze badges

Comments

GolezTrol · Accepted Answer · 2013-06-19 14:41:11Z

3

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.

answered Jun 19, 2013 at 14:41

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Comments

gbn · Accepted Answer · 2013-06-19 14:41:40Z

3

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

answered Jun 19, 2013 at 14:41

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

brunch875 · Accepted Answer · 2021-02-27 20:49:57Z

3

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!

answered Feb 27, 2021 at 20:49

brunch875

8695 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL: Count() based on column value

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related