Post reply

Question re: NULLs and Count function

  • December 14, 2006 at 5:16 pm

    #175288

    A) select count(*) from dependent -- returns 39 rows

    B) select count(*) from dependent where age is null -- returns 3 rows

    C) select count(*) from dependent where age is not null -- returns 36 rows

    D) select count(age) from dependent where age is not null -- returns 36 rows

    E) select count(age) from dependent where age is null -- returns 0 rows

    Why am I getting 0 row returned on query E? I would expect the same count as query B. There are in fact 3 NULL values in the Age column.

    Thanks.

  • December 14, 2006 at 10:01 pm

    #678255

    COUNT(*) returns the number of items in a group, including NULL values and duplicates

    But if you specify a column name in Count function i.e Count(age) then it will not return number of records containing NULL.

    You cannot use where column name is null when column name is specified inside Count function.

  • December 14, 2006 at 10:32 pm

    #678256

    first you tell me what output you want from these queries.

    if you want your query return number of rows then use (b) otherwise use (e)

  • December 15, 2006 at 1:29 am

    #678270

    Count(*) returns the number of rows

    Count(<column&gt returns the number of non-null values in that column.

    In your case,

     select count(age) from dependent where age is not null

    and

     select count(age) from dependent

    will both return the same, is 36 rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 15, 2006 at 1:43 pm

    #678457

    NULL values are eliminated from agregate function results.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 5 posts - 1 through 4 (of 4 total)

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