CASE function for Null values

Question

Post reply

CASE function for Null values

Parthipan-244146

SSCrazy

Points: 2178
More actions
April 16, 2009 at 2:35 am

#131288

Hi All,
The following query
select Category, count(*) as TotalNumber from
(select
Case ITPolicyName
when NULL then 'No Policy Assigned'
ELSE ITPolicyName
END as Category
from table1) as Temp
group by Category
order by TotalNumber desc
is not returning 'No Policy Assigned' as category for Null values in SQL 2005 table.
Please advise.
BR,
Parthi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 1

Sorry experts I solved the problem by

select Category, count(*) as TotalNumber from

(select

Case

when ITPolicyName is NULL then 'No Policy Assigned'

ELSE ITPolicyName

END as Category

from table1) as Temp

group by Category

order by TotalNumber desc

BR,

Parthi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT ISNULL(ITPolicyName, 'No Policy Assigned') AS Category,

COUNT(*) AS TotalNumber

FROM table1

GROUP BY ISNULL(ITPolicyName, 'No Policy Assigned')

ORDER BY 2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden