Help with correlated sub queries

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.