Condition on Query Created Field

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

Condition on Query Created Field

Author

Topic

JBee
Starting Member

9 Posts

Posted - 2007-09-28 : 06:36:40

Hi all,

i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?

Thanks.

select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
--and total > 2 (Wont recognise Total)
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 06:39:29

--and total > 2 (Wont recognise Total)

between GROUP BY and ORDER BY insert:

HAVING COUNT(*) > 2

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 06:42:41

select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
having count(*)>2
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc

or

Select * from
(
select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
group by b.iownerid, a.icompanyid, a.vchcompanyname
) as T
where total>2
order by total desc

Madhivanan

Failing to plan is Planning to fail

JBee
Starting Member

9 Posts

Posted - 2007-09-28 : 07:02:48

Thank you both!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources