Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Counting NULL columns?

    If I had a basic table with 3 VARCHAR fields; let's say A, B, C

    How could I write a query that returns the count of the number of NULL columns for every record in a table?

    Ideally, it would be something like:

    SELECT
    CAST (A IS NULL) AS INTEGER
    + CAST (B IS NULL) AS INTEGER
    + CAST (C IS NULL) AS INTEGER
    FROM MyTable

    That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    select 'a is null', count(*) from mytable where a is null
    union all
    select 'b is null', count(*) from mytable where b is null
    union all
    select 'c is null', count(*) from mytable where c is null
    union all
    select 'all are null', count(*) where (a is null or b is null or c is null)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sum(case when A is null then 1 else 0 end) as Anulls
         , sum(case when B is null then 1 else 0 end) as Bnulls
         , sum(case when C is null then 1 else 0 end) as Cnulls
      from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rudy, I think he wants to count the nulls for each row:

    select case when A is null then 1 else 0 end
    + case when B is null then 1 else 0 end
    + case when C is null then 1 else 0 end as NullValues
    from yourtable
    Last edited by blindman; 05-14-04 at 22:43.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, you are too right

    the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls

    3
    2
    0
    1
    0
    0
    0
    2
    0
    3
    0
    2
    1



    at least my query actually produces something useful

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hey, he only said he wanted the count! Ain't my problem if the project specs are no good...

    And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.

    ... and make Rudy happy by at least including a primary key in your result set!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    many ways to skin the null

  8. #8
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    Rudy, I think he wants to count the nulls for each row:

    select case when A is null then 1 else 0 end
    + case when B is null then 1 else 0 end
    + case when C is null then 1 else 0 end as NullValues
    from yourtable
    Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.

    Thanks Rudy and blindman!

    FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RogerWilco
    I only want to overwrite the existing data if the new data has more non-NULL fields.
    surely the number of non-nulls is of secondary concern

    suppose i had a Three Stooges table with this row:

    'curly','larry',null

    you're saying it's okay to overwrite this with

    'tom','dick','harry'


    but i've done many merges myself, and i'm sure there's more to your example than just three fields...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Consider using this instead:

    Update A
    set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
    A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
    A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
    ..etc..
    from A inner join B on A.PKey = B.Pkey

    This merges the two datasets together, giving priority to data in table A.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Follow the blindman, follow the blindman!!!

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Only when you are in the dark...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice

    might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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