Count function with nulls in columns

  • I have a stored proc that accepts 3 input parms to take the count of rows

    with a where clause

    select count(*) from dbo.tab1 M

    where M.region=@region and M.hub_name=@hub_name and M.account_name=@account_name)

    Unfortunately, my table data is bad (region or hub_name or account_name can be null) and when that happens, the count is incorrect when in actuality there are rows.

    Anyway, I can get the correct counts given the fact that null is allowed in all columns of that table?

    Thanks

  • Count (*) will give you the total number of rows in the result set. Count(column name) give you the number of non-null values in the column.

    If there are nulls in the columns you're filtering on, then you have to allow for nulls so that the rows can appear in the result set.

    where (M.region=@region OR M.region IS NULL) and (M.hub_name=@hub_name OR M.hub_name IS NULL) and (M.account_name=@account_name OR M.account_name IS NULL)

    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
  • GilaMonster (1/19/2008)


    If there are nulls in the columns you're filtering on, then you have to allow for nulls so that the rows can appear in the result set.

    where (M.region=@region OR M.region IS NULL) and (M.hub_name=@hub_name OR M.hub_name IS NULL) and (M.account_name=@account_name OR M.account_name IS NULL)

    eliminate the OR logic by wrapping the column with isnull():

    where isnull(M.region,@region)=@region

    and isnull(M.hub_name,@hub_name)=@hub_name

    and isnull(M.account_name,@account_name)=@account_name

  • Thank you both, Gail and Antonio. Works fine.

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

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