Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    peaceful earth

    Question Unanswered: Column Count Percentage of Not Null fields

    Hello folks,

    I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

    For instance, a row from my table:
    Row1 : field1 field2 field3

    If field3 is empty or null, my query should return 67%.

    So far I have gotten the number of fields:
    select count(1) from information_schema.columns where table_name='myTable'

    I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

    Thanks for your help

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    let me get this straight -- you don't really know which columns the table will have? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    South Africa
    Provided Answers: 1

    Column Count Percentage of Not Null fields

    Do you want to inspect the content of each column in each row OR
    Is your intention instead to find the number of columns in a table that do not allow nulls?

    select object_name(id),colcount=count(*)
    ,notnullablecount=sum(case when isnullable=0 then 1 else 0 end)
    ,nullablecount=sum(case when isnullable=1 then 1 else 0 end)
    ,'Notnullable%'=convert(int,sum(case when isnullable=0 then 1 else 0 end)*100./count(*)+.5)
    from syscolumns
    where id=object_id('myTable')
    group by id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts