5

Is there any way to count all fields with null values for specific record excluding PrimaryKey column?

Example:

ID  Name    Age    City     Zip

1   Alex    32     Miami    NULL
2   NULL    24     NULL     NULL

As output I need to get 1 and 3. Without explicitly specifying column names.

German
  • 740
  • 2
  • 10
  • 24

4 Answers4

15
declare @T table
(
  ID int,
  Name varchar(10),
  Age int,
  City varchar(10),
  Zip varchar(10)
)  

insert into @T values 
(1, 'Alex', 32, 'Miami', NULL),
(2,  NULL,  24,  NULL,   NULL)

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select *
          from @T as T2
          where T1.ID = T2.ID
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

Result:

ID          NullCount
----------- -----------
1           1
2           3

Update:

Here is a better version. Thanks to Martin Smith.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

Update:

And with a bit faster XQuery expression.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(//*/@ns:nil)', 'int') as NullCount
from @T as T1
Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    [I think you can improve the sub query here](http://dba.stackexchange.com/a/14870/3690) – Martin Smith Mar 12 '12 at 20:44
  • @MartinSmith - Yes of course. No need to have the table one more time in there. – Mikael Eriksson Mar 12 '12 at 21:22
  • +1 This worked wonders. Only had 1.1k rows to check for. FYI this also evaluates the textual version of NULL. This got me started, but http://dba.stackexchange.com/questions/14864/test-if-any-fields-are-null/14870#14870 finished me off – GoldBishop Apr 29 '14 at 15:01
3
SELECT id,
  CASE WHEN Name IS NULL THEN 1 ELSE 0 END +
  CASE WHEN City IS NULL THEN 1 ELSE 0 END +
  CASE WHEN Zip  IS NULL THEN 1 ELSE 0 END
FROM YourTable

If you do not want explicit column names in query, welcome to dynamic querying

DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = @sql + N'  CASE WHEN '+QUOTENAME(c.name)+N' IS NULL THEN 1 ELSE 0 END +'
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
WHERE 
    c.is_nullable = 1
AND t.object_id = OBJECT_ID('YourTableName')

SET @sql = N'SELECT id, '+@sql +N'+0 AS Cnt FROM [YourTableName]'
EXEC(@sql)
Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
0
DECLARE @tempSQL nvarchar(max)
SET @tempSQL = N'SELECT '

SELECT @tempSQL = @tempSQL + 'sum(case when ' + cols.name + ' is null then 1 else 0 end) "Null Values for ' + cols.name + '",
            sum(case when ' + cols.name + ' is null then 0 else 1 end) "Non-Null Values for ' + cols.name + '",' FROM sys.columns cols WHERE cols.object_id = object_id('TABLE1');

SET @tempSQL = SUBSTRING(@tempSQL, 1, LEN(@tempSQL) - 1) + ' FROM TABLE1;'

EXEC sp_executesql @tempSQL
Chaitanya
  • 33
  • 1
  • 5
0

This should solve your problem:

select count (id) 
where ( isnull(Name,"") = "" or isnull(City,"") = "" or isnull(Zip,"") = "" )

Not a smart solution, but it should do the work.

Andreas Rohde
  • 613
  • 5
  • 15