MySQL Group By not working in subquery

Question

I have this query and it works great. I use the MIN(home_price) to display as a starting price and I use this query for an api and WHERE clauses get added to it, so if I search by price the MIN(home_price) changes.

SELECT MIN(home_price) as min_home_price,
   id,
   name,
   community,
   maplocation,
   locationLabel,
   logo 
FROM ourCommunity 
INNER JOIN readyBuilt 
   ON community = home_community 
INNER JOIN rb_locations 
   ON readyBuilt.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt 
                        ON community = home_community 
                 WHERE isDeleted = 0 AND is_upcoming = 0) 
   AND home_status = 1 
 GROUP BY id,name,community,mapLocation,locationLabel,logo 
 ORDER BY name

So my solution was to use a subquery

SELECT id,
   name,
   community,
   maplocation,
   locationLabel,
   logo, 
   (SELECT MIN(home_price) as min_home_price 
          FROM ourCommunity 
          INNER JOIN readyBuilt 
                 ON community = home_community 
          INNER JOIN rb_locations 
                 ON readyBuilt.home_location = rb_locations.locationId 
          WHERE id IN ( SELECT DISTINCT id 
                        FROM ourCommunity 
                        INNER JOIN readyBuilt 
                               ON community = home_community 
                        WHERE isDeleted = 0 
                               AND is_upcoming = 0) 
                 AND home_status = 1 
          GROUP BY id,name,community,mapLocation,locationLabel,logo 
          ORDER BY name) as org_min_home_price 
FROM ourCommunity 
INNER JOIN readyBuilt 
   ON community = home_community 
INNER JOIN rb_locations 
   ON readyBuilt.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt 
                        ON community = home_community 
                 WHERE isDeleted = 0 AND is_upcoming = 0) 
   AND home_status = 1 
 GROUP BY id,name,community,mapLocation,locationLabel,logo 
 ORDER BY name

But when I execute the second query, I get this error

Subquery returns more than 1 row

When I remove the GROUP BY I get no errors by the MIN(home_price) is the same for each row. Does anyone have any suggestion on how to accomplish what I am trying to accomplish?

Answer 1

It's normal that if you add a price as filter the minimum price returned won't be the real minimum of the table.

You could join on a result set of each min(home_price) groupped by community

SELECT min_home_price,   
   id,
   name,
   community,
   maplocation,
   locationLabel,
   logo 
FROM ourCommunity
INNER JOIN (select min(home_price) min_home_price, home_community from readyBuilt group by home_community) b on b.home_community = community
INNER JOIN readyBuilt a ON community = a.home_community 
INNER JOIN rb_locations ON a.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt ON community = home_community 
                 WHERE isDeleted = 0) 
AND home_status = 1 
GROUP BY id,name,community,mapLocation,locationLabel,logo 
ORDER BY name;