Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to count in SQL all fields with null values in one record?

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.

like image 248
German Avatar asked Jan 31 '12 11:01

German


2 Answers

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
like image 109
Mikael Eriksson Avatar answered Nov 03 '22 22:11

Mikael Eriksson


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)
like image 23
Oleg Dok Avatar answered Nov 03 '22 23:11

Oleg Dok