SQL group by each tables and join them-CodePudding

select home2012.region_name, avg(home2012.per_price), avg(home2019.per_price)
from home2012 left join 
(select region_name, avg(per_price) from home2019 group by region_name) 
on home2012.region_name = home2019.region_name
group by region_name;

I want to group by region_name each table(home2012, home2019) and left join them by same region_name(after group by). But it occurs error.

CodePudding user response：

Make two subselects and join them

SELECT
    t2012.region_name, t2012.avgprice2012, t2019.avgprice2019
FROM
    (SELECT 
        home2012.region_name, AVG(home2012.per_price) avgprice2012
    FROM
        home2012
    GROUP BY region_name) AS t2012
        LEFT JOIN
    (SELECT 
        region_name, AVG(per_price) avgprice2019
    FROM
        home2019
    GROUP BY region_name) AS t2019 ON t2012.region_name = t2019.region_name
;

CodePudding user response：

 select 
         home2012.region_name,
         avg(home2012.per_price), 
         avg(home2019.per_price)
    from 
        home2012 
        left join  (select region_name, avg(per_price) from home2019 group by 
                    region_name)  on home2012.region_name = home2019.region_name

    group by region_name;

what is the use of joining as mentioned above? does it make any difference rather than increasing one step workload on server.

 select 
         home2012.region_name,
         avg(home2012.per_price), 
         avg(home2019.per_price)
    from 
        home2012 
        left join home2019 on home2012.region_name = home2019.region_name

    group by region_name;