CODEWITHSUNDEEP
sqlsql-servercountnull
sebekkg
sebekkg

Reputation: 17

Count null or non null values for every column in a table

I need help with coding counter for null/non null values for all tables in a table. I know how to do it with one column but don't know how to incorporate it with all columns.

IN EXAMPLE 

              CITY         STATE       CONTINENT
MICHAEL.E     NULL         NY          NA
JOHN.D        PARIS        FR          EU
NICOLAS.B     FRANKFURT    GE          EU
MING.Y        NULL         NULL        AF

So I would get results like 

column      null       not_null ....
city        2          2
state       1          3
continent   0          4

I am certain that it has to be used with list of all column IDs that can be get from 

SELECT DISTINCT AC.object_id, AC.name, AC.column_id 
     FROM SYS.all_columns AC JOIN sys.all_objects AO 
       ON AC.object_id=AO.object_id 
      WHERE AO.name='MY_TABLE_NAME'

And since i have many tables to deal with and many of them have 600+ columns I would need solution for that application.

Upvotes: 0

Views: 2547

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use APPLY :

select cols as [column], 
       sum(case when col is null then 1 else 0 end) as [null],
       sum(case when col is not null then 1 else 0 end) as [not_null]
from table t cross apply
     ( values ('city', city), 
              ('state', state), 
              ('continent', continent),
              . . .
     ) tt (cols, col)
group by cols;

EDIT :

select cols, 
       sum(case when col is null then 1 else 0 end) as Null_value, 
       sum(case when col is not null then 1 else 0 end) as not_null_value 
from TABLE_NAME cross apply 
     ( values ('Column_1', Column_1), 
              ('Column_2', Column_2), 
              ('Column_3', Column_3), 
              ('Column_4', Column_4), 
              ('Column_5', Column_5)   
     ) tt (cols,col) 
group by cols;

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

SQL Server and Oracle

with CTE as
(
  select ColName, ColVal
  from 
  (
    select name, city, state, continent
    from mytable
  ) t1
    unpivot
  (
    ColVal for ColName in (city, state, continent)
  )
)
select ColName,
       sum(case when ColVal is null then 1 else 0 end) as nulls,
       sum(case when ColVal is null then 0 else 1 end) as not_nulls
from CTE

Upvotes: 0

Related Questions