Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sreesang
sreesang
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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))
Robert Murphy UK1
Robert Murphy UK1
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 237
The field that you are COUNT()'ing, is there a possibility it contains NULLs?

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5782 Visits: 7133
an example could be to use ISNULL() or COALESCE() on the columns over which aggregation is being effected ,e.g. SUM(ISNULL(Column1,0))

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
sreesang
sreesang
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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?
sreesang
sreesang
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5782 Visits: 7133
The warning is merely a notification regarding the possibility of missing data due to the elimination of NULL records in the aggregation.
This warning is only visible in management studio; it will not reflect in any other application, report or job output.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5782 Visits: 7133
A possible way to effect this could be, e.g. SUM(CASE WHEN ColumnName IS NULL THEN 0 ELSE 1 END) in lieu of COUNT(ColumnName)

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
sreesang
sreesang
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Thanks Stewart.. It worked as good... Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search