Post reply

Gotcha! SQL Aggregate Functions and NULL

  • April 5, 2008 at 5:00 pm

    #798822

    rippleshah (4/5/2008)

    Mike,

    I've started reading your article and am not clear why the query: SELECT COUNT(region) FROM Suppliers WHERE Region IS NOT NULL returns 0

    1. WHERE clause eliminates every row in which region is not NULL.

    Therefore, I only have 9 rows where Region IS Not Null

    3. The COUNT(region) function eliminates every row in which region is NULL.

    I would still expect 9 rows still to be returned back after the select query is parsed.

    If you could please elaborate on why 0 rows are returned back from the query.

    SELECT COUNT(region)

    FROM Suppliers

    WHERE region IS NULL

    WHERE region IS NULL -- eliminates all rows where the region is not null

    COUNT(region) -- eliminates all rows where the region is null

    Final result = Total # of rows in table - # of rows where region is not null - # of rows where region is null = 0 rows

  • April 9, 2008 at 8:42 am

    #800467

    Mike,

    Another great article - I have added this to my "briefcase." This gave me good information that I didn't know about these functions. The way you organized the article was very nice as well - I like the individual summaries throughout.

    Thanks,

    Ian.

    "If you are going through hell, keep going."
    -- Winston Churchill

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply