# Help with correlated sub queries

**URL:** <https://forums.percona.com/t/help-with-correlated-sub-queries/1615>\
**Category:** Other MySQL® Questions\
**Created:** [February 20, 2011, 4:33pm UTC](https://forums.percona.com/t/help-with-correlated-sub-queries/1615 "2011-02-20T16:33:39Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sd22](https://avatars.discourse-cdn.com/v4/letter/s/7ba0ec/32.png) [@sd22](https://forums.percona.com/u/sd22)\
**Post date:** [February 20, 2011, 4:33pm UTC](https://forums.percona.com/t/help-with-correlated-sub-queries/1615/1 "2011-02-20T16:33:39Z")

</div>

Hello, here is my query. the actual query is a little more complicated but similar… It returns an error because it cannot find the first query variable - s1.ShopCity

any help is appreciated. thank you!!!

select  
s1.ShopCity,  
(select count(\*) from (select ShopID \> 1 from tblShops s2 where s2.ShopCity = s1.ShopCity) as totalcount) as tcount

from tblShops s1  
group by s1.ShopCity

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [February 23, 2011, 1:09pm UTC](https://forums.percona.com/t/help-with-correlated-sub-queries/1615/2 "2011-02-23T13:09:24Z")

</div>

And you are sure that s1.ShopCity is the correct spelling and that it is present in the tblShops table?

But regardless I recommend you to rewrite your query to something like:

select city.city ,count(s1.ShopCity) as tcountfrom ( SELECT s2.ShopCity as city FROM tblShop s2 GROUP BY s2.ShopCity ) as cityLEFT JOIN tblShop s1 ON city.city = s1.ShopCity AND s1.ShopID \> 1group by city.city

The reason is that the derived table city will only be evaluated once and reduced to all unique cities = fewer records, while in your case the subselect will be evaluated for every row of the tblShop table.