how do I over come this? - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

how do I over come this?

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-12-10 : 16:09:00

Error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Query:
Select distinct owner_agent_login, Count(comm_id) as Corr_Count
from
Correspondences
left join Agents on owner_agent_login = agent_login_id
where owner_agent_login in ('andrewgo')
and comm_date_utc between '2013/11/01' and '2013/11/30'
and MAX(Start_Date) < DATEADD(day,DATEDIFF(day,0,'2013/11/30')-90,0)
group by owner_agent_login

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-10 : 16:35:27

Can you explain what you are trying to do? That query is in bad shape: No table aliases, MAX as a predicate, etc..

Here are some links that might help you ask your question so we can help you better (like including sample data and expected output):
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-10 : 16:38:35

Since you have no table aliasing at all, it's hard to help you.
This will however give you an idea

SELECT		c.Owner_Agent_Login,
		COUNT(a.Comm_ID) AS Corr_Count
FROM		dbo.Correspondences AS c
LEFT JOIN	dbo.Agents AS a ON a.Agent_Login_ID ON c.Owner_Agent_Login
WHERE		c.Owner_Agent_Login IN ('AndrewGo')
		AND c.Comm_Date_UTC BETWEEN '20131101' AND '20131130'
GROUP BY	c.Owner_Agent_Login
HAVING		MAX(c.[Start_Date]) < DATEDIFF(DAY, 90, '20131130');

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources