aggregate function in where clause

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 2005 Forums

Transact-SQL (2005)

aggregate function in where clause

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-11-25 : 16:31:20

Hello,

I have this query this query set up

select b.person_id,max(b.create_timestamp)
from person a
join patient_encounter b on a.person_id = b.person_id
where max(b.create_timestamp) >= '01/01/2008' and max(b.create_timestamp) <= '12/31/2008'
group by b.person_id

I get this 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.

Could someone show me what is meant by this?

Thanks in Advance!
Sherri

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 16:33:08

quote:
Originally posted by sross81

Hello,

I have this query this query set up

select b.person_id,max(b.create_timestamp)
from person a
join patient_encounter b on a.person_id = b.person_id
group by b.person_id
having max(b.create_timestamp) between '01/01/2008' and '12/31/2008'
I get this 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.

Could someone show me what is meant by this?

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-11-25 : 16:34:43

Thanks that works perfectly. I don't understand why there has to be a where and a having clause..... :)

quote:
Originally posted by sodeep

quote:
Originally posted by sross81

Hello,

I have this query this query set up

select b.person_id,max(b.create_timestamp)
from person a
join patient_encounter b on a.person_id = b.person_id
group by b.person_id
having max(b.create_timestamp) between '01/01/2008' and '12/31/2008'
I get this 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.

Could someone show me what is meant by this?

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources