3. stock1.sql

	#subquery: trying to calculate number of orders per pizza name and combining with recipe and ingredients
    #superquery: #calculating total weight ordered of each ingredient: (number of pizzas ordered of a kind * the ingredient quantity required for one pizza)
				#also calculating the unit cost= total cost of the ingredient in inventory/total weight in inventory
                #finally multiplying the weight of the ingredient required with the unit cost to calculate the total cost of the pizza (only as per what its contents are)
    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_weight,
		ing.ing_price,
		ing.ing_name) s1