CODEWITHSUNDEEP
mysql
margherita pizza
margherita pizza

Reputation: 7185

MySQL get summation values from separate tables

regions

id | name
---+---------
1  | NY
2  | Paris
3  | London

restaurants

id | name      | region_id
---+-----------+----------
1  | kFC       |1
2  | McDonalds |2

banner_ads

id | image | fee  | region_id
---+-------+------+-----------
1  |1.jpg  |500   | 1
2  |2.jpg  |500   | 1
3  |3.jpg  |500   | 2
4  |4.jpg  |500   | 3

cuisine_promotions

id | cuisine_id | fee  | region_id
---+------------+------+-----------
1  |1           |500   | 1
2  |2           |500   | 1
3  |3           |500   | 2
4  |4           |500   | 3

sponsored_promotions

id | item_id | fee  | restaurant_id
---+---------+------+--------------
1  |1        |500   | 1
2  |2        |500   | 1
3  |3        |500   | 2
4  |4        |500   | 2

I want to populate data to this table

region | banner_revenue | cuisine revenue | promotions_revenue | total
-------+----------------+-----------------+--------------------+--------
NY     |   1000         |   1000          | 1000               | 3000
Paris  |    500         |    500          | 1000               | 2000
London |    500         |    500          | null               | 1000

I could do something like this

SELECT sum(fee) FROM test2.banner_ads;

SELECT sum(fee) FROM test2.cuisine_promotions;

SELECT 
    regions.name,
    SUM(sponsored_promotions.fee) 
FROM
    test2.sponsored_promotions 
INNER JOIN
    restaurants ON sponsored_promotions.restaurant_id = restaurants.id
INNER JOIN
    regions ON restaurants.region_id = regions.id
GROUP BY 
    regions.name;

And sum up the above three value to get total.

But I am looking for more elegant way to do this.

Is it possible to do this in a single query? Without having 3 separate queries?

Upvotes: 3

Views: 37

Answers (2)

Kamran Feroz
Kamran Feroz

Reputation: 116

@TimBiegeleisen You've beat me by time :)... was working on the query, but when I refresh the page saw that you'e already replied... Great work...! btw, I wrote the following query for this....

SELECT r.name Region, IFNULL(ba.fee, 0) banner_revenue, IFNULL(cp.fee, 0) cuisine_revenue, IFNULL(sp.fee, 0) promotions_revenue, (IFNULL(ba.fee, 0) + IFNULL(cp.fee, 0) + IFNULL(sp.fee, 0)) total
FROM regions r 
	LEFT JOIN (SELECT SUM(sp.fee) fee, re.region_id FROM sponsored_promotions as sp INNER JOIN restaunts as re ON re.id = sp.restaurant_id GROUP BY re.region_id) sp ON r.id=sp.region_id
    LEFT JOIN (SELECT IFNULL(SUM(fee),0) fee, region_id FROM `cuisine_promotions` GROUP BY region_id) cp ON r.id=cp.region_id
    LEFT JOIN (SELECT IFNULL(SUM(fee),0) fee, region_id FROM `banner_ads` GROUP BY region_id) ba ON r.id=ba.region_id

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I would write this as a series of joins between subqueries, each of which does an aggregation to find a certain revenue amount.

SELECT
    r.name,
    COALESCE(b.banner_revenue, 0) AS banner_revenue,
    COALESCE(c.cuisine_revenue, 0) AS cuisine_revenue,
    COALESCE(p.promotions_revenue, 0) AS promotions_revenue,
    COALESCE(b.banner_revenue, 0) + COALESCE(c.cuisine_revenue, 0) +
        COALESCE(p.promotions_revenue, 0) AS total
FROM regions r
LEFT JOIN
(
    SELECT region_id, SUM(fee) AS banner_revenue
    FROM banner_ads
    GROUP BY region_id
) b
    ON r.id = b.region_id
LEFT JOIN
(
    SELECT region_id, SUM(fee) AS cuisine_revenue
    FROM cuisine_promotions
    GROUP BY region_id
) c
    ON r.id = c.region_id
LEFT JOIN
(
    SELECT r.region_id, SUM(s.fee) AS promotions_revenue
    FROM restaunts r
    INNER JOIN sponsored_promotions s
        ON r.id = s.restaurant_id
    GROUP BY r.region_id
) p
    ON r.id = p.region_id;

Follow the demo link below to see the query working correctly with your data.

Demo

Upvotes: 2

Related Questions