CODEWITHSUNDEEP
sqlsql-server-2008
German
German

Reputation: 740

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.

Upvotes: 5

Views: 7280

Answers (4)

Chaitanya
Chaitanya

Reputation: 33

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 15

Oleg Dok
Oleg Dok

Reputation: 21756

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)

Upvotes: 3

Andreas Rohde
Andreas Rohde

Reputation: 609

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.

Upvotes: 0

Related Questions