Eliminate warning due to aggregate functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Eliminate warning due to aggregate functions

Author

Topic

SreeSang
Starting Member

2 Posts

Posted - 2012-02-07 : 04:09:24

Hi,

When I tried executing the below query I get the
Warning: Null value is eliminated by an aggregate or other SET operation.

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))

Need to modify this query appropriately to eliminate warnings. Any suggestions?

SreeSang
Starting Member

2 Posts

Posted - 2012-02-07 : 04:22:01

The warning is because of COUNT(T2.Columnname2) Since there are some records in T1 but not in T2.
Need to modify the query so as to eliminate nulls in T2.Columnname2.
Any idea?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-07 : 04:58:55

Will it make sense?
The warning is only telling you that for missing rows in T2 the count() will not count.
Do you want the count() to count a row that isn't present?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources