This should be easy! But not for me!

[DBAssam]

Please could someone point me in the right direction. I am pulling data into graphs from a table and use:

SELECT
(CASE
WHEN count (worktype) > 4
THEN worktype
END) as 'worktype',
(CASE
WHEN count (worktype) > 4
THEN count(*)
END) as 'cnt'
from table1
group by worktype

This gives the desired results but also includes the NULL values as well:

Driver 57
NULL NULL
Painter 6
Carpenter 17
Nurse 73
NULL NULL

How can I get rid of the NULLS that represent worktypes with less than 4 employees? Or, even better, how do I catch the values that are less than 4 and add them to show 'OTHER JOB' with the sum of the values < 4?

Any help will be greatly appeciated.

Thanks

 

[AWithers]

add

'where worktype is not null'

to the end of the sql string

Andy

 

[DBAssam]

Sorry Andy - tried that - doesn't work. Likely as it is finding values, but they just happen to be less than 4. Cheers anyway.

 

[mwolf00]

HAVING count (worktype) > 4 ????

 

[cdukes]

Try the following

SELECT
(CASE
WHEN count (worktype) > 4 THEN worktype
ELSE 'OTHER JOB'
END) as 'worktype',
(CASE
WHEN count (worktype) > 4 THEN count(*)
ELSE 0
END) as 'cnt'
from table1
group by worktype

 

[DBAssam]

Thanks all - mwolf00's HAVING statement did the trick.

Cheers

D