count of null values in columns

count of null values in columns

• Rajith

  Since I know row count I expect that non-NULLS or NULLS are the same for me.

  System tables would be the solution, however, if the MyTable is not physically a table that won't help. Like SELECT thisstuff FROM (SELECT ...). And yes, since I'm not so experienced in SQL I just asked in case I'm missing some obvious elegant way. :)

  This perhaps answers Laskhmana solution, which I guess is pretty similar idea.

  Well... actually I was asking a year ago so I'll have to open some older project to answer your question, however I can't do it now. If I'll find out, I will let you know.

  
  

• nbaker

  This still requires you to know col01 and col02 names.

  imagine table

  A	B
  x	NULL
  NULL	NULL
  x	NULL

  What I'm looking for is something like

  SELECT COUNTSofNULLSinCOLUMNS(*) FROM MyTable

  which would return

  1

  3

  You know I need to not specify any column names since I don't know them.

  
  

• DevDiver

  If you're looking for an aggregate to count NULLS, you'll need to use a grouping function (such as GROUP BY) for that. Note that this function counts non-NULLS rather than NULLS, and still requires a name of the columns to work.

  You could create a cursor of the column names by joining system tables to loop through them, but that sounds pretty un-elegant.

  Why do you need this information Are you looking for data density information

  
  
  

• Ashish.Net

  Hu! I dont know how to do this which lets you use a "SELECT" syntax.

  But this can be done with a proc which takes the tables name as an input param and genrates an SQL statement dynamically to replace COL01, COL02... CONNN with acutal columns and give the same result.

  Do you know how to do this If not, wait for few minutes!

  
  
  

• B M E

  Hi!

  Thank you for your reply, however, this works only for one column. I meant how to do that if I don't know which columns are present, eg if I use SELECT * command...

  
  

• CIB

  Here is my way:

  select
  coutOfNullsInCol01 = sum(case when col01 is null then 1 else 0 end),
  coutOfNullsInCol02 = sum(case when col02 is null then 1 else 0 end)
  from
  Table

  
  
  

• MF Newbie

  here is the sample code (please note that this can be writen in a better way.):

  declare

  @tableName sysname

  set @tableName = 'TableWithFewColumns'

  -- you can create a proc with following code with above one as param

  declare

  @strSQL varchar(8000)

  if object_id('tempdb..#columns') is not null
  drop table #columns

  create table #columns(colName varchar(255))

  insert into #columns
  exec('select name from syscolumns (nolock) where id = object_id(''' + @tableName + ''')')

  set @strSQL = ''

  select
  @strSQL = @strSQL + ',coutOfNulls' + colName + ' = sum(case when ' + colName + ' is null then 1 else 0 end)'
  from
  #columns

  set @strSQL = 'select ''' + @tableName + ''' as SourceTableName' + @strSQL + ' from ' + @tableName

  exec(@strSQL)

  
  
  

• Md s

  SELECT COUNT(foo) WHERE Foo IS NULL;

  GO

  
  
  
• count of null values in columns