Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Warning: Null value is eliminated by an aggregate or other SET operation. Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2012 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 5:16 AM
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))
Post #1247903
Posted Tuesday, February 7, 2012 2:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:38 AM
Points: 283, Visits: 226
The field that you are COUNT()'ing, is there a possibility it contains NULLs?


Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
Post #1247908
Posted Tuesday, February 7, 2012 2:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
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”
Post #1247909
Posted Tuesday, February 7, 2012 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 5:16 AM
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?
Post #1247912
Posted Tuesday, February 7, 2012 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 5:16 AM
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.
Post #1247916
Posted Tuesday, February 7, 2012 2:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
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”
Post #1247921
Posted Tuesday, February 7, 2012 3:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
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”
Post #1247925
Posted Tuesday, February 7, 2012 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 5:16 AM
Points: 4, Visits: 9
Thanks Stewart.. It worked as good... :)
Post #1248005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse