CODEWITHSUNDEEP
sqlcoalesce
Claire Hart
Claire Hart

Reputation: 33

Bringing together coalesce, count, case and clauses

I'm new to this so please bear with me.

I'm writing a query where I need to count the number of rows with two specific values,

I have used the following to get the result of the different values in one field but I need to know the results only if another field is set to a specific value. I pulled the following from a previous question on this site:

COALESCE(count(case when CW.MAINJOBROLE = 2 THEN 1 end),0) as ALLJOBROLES_2,
coalesce(count(case when CW.MAINJOBROLE = 3 then 1 end), 0) as ALLJOBROLES_3,
coalesce(count(case when CW.MAINJOBROLE = 4 then 1 end), 0) as ALLJOBROLES_4,
coalesce(count(case when CW.MAINJOBROLE = 7 then 1 end), 0) as ALLJOBROLES_7,
coalesce(count(case when CW.MAINJOBROLE = 8 then 1 end), 0) as ALLJOBROLES_8,
coalesce(count(case when CW.MAINJOBROLE = 23 then 1 end), 0) as ALLJOBROLES_23,
coalesce(count(case when CW.MAINJOBROLE = 24 then 1 end), 0) as ALLJOBROLES_24,
coalesce(count(case when CW.MAINJOBROLE = 25 then 1 end), 0) as ALLJOBROLES_25'

As part of a larger query, I want to do the above only if CW.EMPLSTATUS = 1

Upvotes: 3

Views: 13721

Answers (3)

programmer43229
programmer43229

Reputation: 386

I believe you want to use SUM() and not COUNT().

SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 2 THEN 1 end) as ALLJOBROLES_2,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 3 then 1 end) as ALLJOBROLES_3,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 4 then 1 end) as ALLJOBROLES_4,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 7 then 1 end) as ALLJOBROLES_7,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 8 then 1 end) as ALLJOBROLES_8,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 23 then 1 end) as ALLJOBROLES_23,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 24 then 1 end) as ALLJOBROLES_24,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 25 then 1 end) as ALLJOBROLES_25

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You will have to surround each expression with another CASE WHEN construct:

CASE WHEN CW.EMPLSTATUS = 1 THEN
  count(case when CW.MAINJOBROLE = 2 THEN 1 end)
ELSE
  NULL
END as ALLJOBROLES_2,
CASE WHEN CW.EMPLSTATUS = 1 THEN
  count(case when CW.MAINJOBROLE = 3 THEN 1 end)
ELSE
  NULL
END as ALLJOBROLES_3,
....

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can just add the condition into the where clause:

COALESCE(count(case when CW.MAINJOBROLE = 2 and CW.EMPLSTATUS = 1 THEN 1 end),0) as ALLJOBROLES_2,

By the way, the COALESCE() should be unnecessary. COUNT() will return 0 if there are no matches.

Upvotes: 4

Related Questions