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

Popular posts from this blog

jasper reports - Fixed header in Excel using JasperReports -

media player - Android: mediaplayer went away with unhandled events -

python - ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000') or TypeError: 'tuple' object is not callable -