SQL-Project/ingredient_cost_query.sql at main · Sabdikay/SQL-Project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

SELECT
    s1.item_name,
    s1.ing_id,
    s1.ing_name,
    s1.order_quantity,
    s1.recipe_quantity,
    s1.order_quantity * s1.recipe_quantity AS ordered_weight,
    s1.ing_price / s1.ing_weight AS unit_cost,
    (s1.order_quantity * s1.recipe_quantity) * (s1.ing_price / s1.ing_weight) AS ingredient_cost
FROM (
    SELECT
        o.item_id,
        i.sku,
        i.item_name,
        r.ing_id,
        ing.ing_name,
        r.quantity AS recipe_quantity,
        SUM(o.quantity) AS order_quantity,
        ing.ing_weight,
        ing.ing_price
    FROM
        Orders o
        LEFT JOIN Item i ON o.item_id = i.item_id
        LEFT JOIN Recipe r ON i.sku = r.recipe_id
        LEFT JOIN Ingredient ing ON ing.ing_id = r.ing_id
    GROUP BY
        o.item_id, i.sku, i.item_name, r.ing_id, r.quantity, ing.ing_name, ing.ing_weight, ing.ing_price
) s1;