CODEWITHSUNDEEP
mysqlsql
SuperDJ
SuperDJ

Reputation: 7661

Calculate with values from joined table

I have the following tables of which I would like to calculate the total of calories and price.

Cake:

| id | cake      | description |
|  1 | chocolate |             |

Recipe:

| id | cake_id | ingredient_id |
|  1 |       1 |             1 |
|  2 |       1 |             2 |

ingredients:

| id | ingredient | calories | buy_price | sell_price |
|  1 | Chocolate  |      200 |     12.22 |      14.00 |
|  2 | Sugar      |      300 |     10.50 |      11.50 |

So the expected result would be calories => 500, buy_price => 22.72 and sell_price => 15.50

I already tried the following query but that didn't work.

SELECT `c`.`id`, `cake`, `description`
FROM `cakes` `c`
JOIN `recipes` `r`
ON `c`.`id` = `r`.`cakes_id`
CROSS JOIN (
    SELECT `calories`, `buy_price`, `sell_price`
    FROM `ingredients` `i`
    WHERE `i`.`id` = `r`.`ingredients_id`
    ) `ingredients`

It gives me an error that r`.`ingredients_id is unknown which I can understand but I don't know how to fix it. But that still leaves me with the question of how to calculate the values.

I hope someone can help me with this question.

Upvotes: 0

Views: 108

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30849

Can you try the following query:

SELECT c.id, SUM(i.calories), SUM(i.buy_price), SUM(i.sell_price)
FROM cake c
JOIN recipe r ON c.id = r.cake_id
JOIN ingredients i ON r.ingredient_id = i.id
GROUP BY c.id;

Upvotes: 2

Related Questions