sql - Filtering based on COUNT -
i have query works well. trying add filter if users_in_this_country
> 1. know add users_in_this_country > 1
where. if add inside parenthesis says invalid column , same if add outside of parenthesis. dumb , easy, on looking? thanks!
select u.contactname ,cu.[user id] ,c.name ,c.id ,cu.[foreign table] ,count(*) on (partition c.id) user_in_this_country dbo.country c inner join dbo.countryuser cu on c.id = cu.[foreign id] inner join dbo.usercolder u on cu.[user id] = u.id exists ( select * countryuser cu2 cu2.[foreign id] = cu.[foreign id] , cu2.[user id] <> cu.[user id] , cu.[foreign table] = 'country')
with aggregate functions (like count) used in conjuction on clause have use cte or subquery, this
cte ( select u.contactname ,cu.[user id] ,c.name ,c.id ,cu.[foreign table] ,count(*) on (partition c.id) user_in_this_country dbo.country c inner join dbo.countryuser cu on c.id = cu.[foreign id] inner join dbo.usercolder u on cu.[user id] = u.id exists ( select * countryuser cu2 cu2.[foreign id] = cu.[foreign id] , cu2.[user id] <> cu.[user id] , cu.[foreign table] = 'country') ) select * cte user_in_this_country > 1
Comments
Post a Comment