Gotcha! SQL Aggregate Functions and NULL

• Mike C

  SSC-Insane

  Points: 23224

  More actions

  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

• Ian Crandell

  SSCommitted

  Points: 1603

  More actions

  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