Warning: Null value is eliminated by an aggregate or other SET operation.

  • When I try to execute the below query, I get the warning

    Warning: Null value is eliminated by an aggregate or other SET operation.

    any idea how to eliminate this?

    SELECT

    T1.Columnname1

    ,ISNULL(T3.Columnname3,10) as Columnname3

    ,COUNT(T2.Columnname2) as Columnname2, ISNULL(T3.Columnname3,10) - COUNT(T2.Columnname2)

    FROM Table1 T1 WITH (NOLOCK)

    LEFT JOIN Table2 T2 WITH (NOLOCK) ON T1.Columnname1 = T2.Columnname5

    LEFT JOIN Table3 T3 WITH (NOLOCK) ON T1.Columnname1 = T3.Columnname6 AND T3.Columnname4 = 85

    WHERE T1.Columnname1<> -1

    GROUP BY T1.Columnname1,T3.Columnname3,T2.Columnname5

    HAVING ISNULL(T3.Columnname3,10) > COUNT(T2.Columnname2)

    ORDER BY (CAST (COUNT(T2.Columnname2) as DECIMAL)/ISNULL(T3.Columnname3,10))

  • The field that you are COUNT()'ing, is there a possibility it contains NULLs?

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
  • This was removed by the editor as SPAM

  • Yes bob. Since I'm getting the count of a column tat is left joined by a table 1, there are records listed that are not in T2.

    But I can't filter not null records in where clause either since it'll become a inner join.

    Need to modify the query accordingly. Can u help me out on this?

  • The problem is there are no null values in T2.Columnname2 as such. But since it is left joined with T1, there are some null records as a result of this. So if I use count(isnull(T2.Columnname2,0)), the count will not be correct.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Thanks Stewart.. It worked as good... 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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