SQL Statement Count NULL Values

[Chad S]

Hello,

Thanks for helping me with this... I really appreciate it.

I have a table called tblPatientDemographics with a number of columns.
I would like to count the number of NULL values per record within my
table.

tblPatientDemographics
PatientID Age Weight Height Race
1234567 20 155 <NULL> Caucasian
8912345 21 <NULL> <NULL> <NULL>

In the first example above I want to display '1'
In the second example above I want to display '3'

Any help would be very much appreciated.

Thanks !

Chad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Here are two possibilities:

SELECT
CASE WHEN patientid IS NULL THEN 1 ELSE 0 END+
CASE WHEN age IS NULL THEN 1 ELSE 0 END+
CASE WHEN weight IS NULL THEN 1 ELSE 0 END+
CASE WHEN height IS NULL THEN 1 ELSE 0 END+
CASE WHEN race IS NULL THEN 1 ELSE 0 END
FROM tblPatientDemographics

SELECT 5-COUNT(patientid)-COUNT(age)-COUNT(weight)-COUNT(height)-COUNT(race)
FROM tblPatientDemographics
GROUP BY <primary key column(s)>

--
David Portas
SQL Server MVP
--

[Chad S]

Hello,

Thanks for your two suggestions. But is there a way to count the NULL
values using * ? Count(*) WHERE IS NULL ? I want to avoid listing out
each column name because there is probably about 20 or so columns in
that table.

Thanks,

Chad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

There isn't a way to do it without listing the columns unless you resort to
dynamic SQL. For reasons of reliability and ease of maintenance it's
generally a good idea to list columns by name in a query. Avoid using SELECT
* in production code. Query Analyzer lets you drag a list of column names
from the Object Browser into the query window, which can save some typing
effort.

--
David Portas
SQL Server MVP
--