SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT columns containing NULL values (MSSQL)

    Hi all,

    For some reason when I attempt to perform a COUNT function on a column to COUNT all the entries that contain a NULL value my result is always '0'. But when I perform a query to get only the results that contain a NULL value in this particular column I get return all the rows that contain NULL value.

    Why does the COUNT function not pick up the NULL values? Does it not work with columns that contain NULL values?

    Thanks

    Tryst

  2. #2
    SitePoint Member zubinraj's Avatar
    Join Date
    Nov 2004
    Location
    Bangalore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Count function does pick up the NULL values properly.

    Try this out in the Northwind database.

    select count(*) as cnt from Employees where Region is NULL

    will return the count of records where region is null.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    count(*) counts rows and doesn't even look at nulls

    count(columnname) exludes nulls
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    count(*) counts rows and doesn't even look at nulls

    count(columnname) exludes nulls
    Though as much, as my query results were looking inconsistent.

    Thanks

    Tryst

  5. #5
    SitePoint Member zubinraj's Avatar
    Join Date
    Nov 2004
    Location
    Bangalore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    NULLs in aggregate functions are ignored; for example, if you're adding up and averaging say, incomes, the person with a null income will be ignored. Well and good if his income was null; not so hot if some silly person forgot to enter the value! Note that there is a useful property of the count function - if you provide it with a constant (eg 1) as an argument, it counts all the rows in the table, but if you provide it with an expression, it looks for all column names within that expression, and then excludes rows containing NULL values for any one of those columns!


Bookmarks

Posting Permissions

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