MySQL get the sum with left joins

Question

I've three tables.

1. REGIONS
2. CUISINE
3. BANNERS

If I run this query

SELECT SUM(fee) FROM BANNERS;

Output will be 10,000

If I run this query

SELECT SUM(fee) FROM CUISINE;

Output will be 12,800

But if I run this query

SELECT REGIONS.name,
sum(BANNERS.fee) as banner_revenue,
sum(CUISINE.fee) as cuisine_revenue
FROM REGIONS
LEFT JOIN BANNERS ON REGIONS.id = BANNERS.region_id
LEFT JOIN CUISINE ON REGIONS.id = CUISINE.region_id
GROUP BY REGIONS.name;

Output is wrong. My desired output is

name    | banner_revenue | cuisine_revenue
------------------------------------------
NY      | 10,000         | 4,800
Paris   | NULL           | 8,000
London  | NULL           | NULL

DB fiddle reproduce

Why could this happen? Please refer my DB fiddle.

Answer 1

Consider the following:

SELECT r.name
     , x.header
     , x.fee 
  FROM REGIONS r
  LEFT 
  JOIN 
     ( SELECT 'banner' header, region_id, fee FROM banners
       UNION
       SELECT 'cuisine', region_id, fee FROM cuisine
     ) x
    ON x.region_id = r.id 
 ORDER  
    BY r.name;


    +--------+---------+------+
    | name   | header  | fee  |
    +--------+---------+------+
    | London | NULL    | NULL |
    | NY     | cuisine | 2500 |
    | NY     | cuisine | 2300 |
    | NY     | banner  | 2000 |
    | NY     | banner  | 5000 |
    | NY     | banner  | 3000 |
    | Paris  | cuisine | 8000 |
    +--------+---------+------+

Answer 2

If you run

SELECT *
       FROM REGIONS
            LEFT JOIN BANNERS
                      ON REGIONS.id = BANNERS.region_id
            LEFT JOIN CUISINE
                      ON REGIONS.id = CUISINE.region_id;

you'll notice, that for every region banner pair all the cusines are join, thus "multiplying" the cuisins. I.e. their fees also multiply.

Do the grouping in the derived tables and join them to get your desired result.

SELECT r.name,
       sb.fee,
       sc.fee
       FROM REGIONS r
            LEFT JOIN (SELECT sum(b.fee) fee,
                              b.region_id
                              FROM BANNERS b
                              GROUP BY b.region_id) sb
                      ON sb.region_id = r.id
            LEFT JOIN (SELECT sum(c.fee) fee,
                              c.region_id
                              FROM CUISINE c
                              GROUP BY c.region_id) sc
                      ON sc.region_id = r.id;