How can i use sum ? with more then 2 key

This is my table structure.

I want to use SUM for Ingredients.Recipe_Ingredient_Gr and Ingredients.Recipe_Ingredient_Piece for each Recipe in selected order.

My english is not enough to tell u exactly what i mean but i’ll try to tell u with example.

in my program user will create stock data and after that they’ll create recipes for food and then they create Orders and add recipes in that orders so what i want to do is when they select order and press calculate they’ll know how much total gr Ingredient they need for that order.

i’m looking for sum command but i dont know how to use it with this table structure

Edit*

This is my table structure

> Stock(ID(PK),Ingredient_Gr,Ingredient_Piece,Ingredient_Name,Unit_Price,Total_Price)
> Ingredient(Stock_ID(FK),Recipe_ID(FK),Recipe_Ingredient_Gr,Recipe_Ingredient_Piece,Ingredient_ID(PK))
> Recipes(Recipe_Name,Recipe_Instructions,Recipe_ID(PK))
> Recipes_Orders(Recipes_ID(FK),Order_ID(FK),Recipes_Orders_ID(PK))
> Orders(Order_ID(PK),Date_Start,Date_End,Order_Piece,Date_Range,Range_Piece,Order_Name)

Desired result for example

Let’s say for order_ID 1 (we have range_piece 200)

we have Recipe_ID 2 and 3

Recipe_ID 2 = Ingredient_ID 4 5gr and Ingredient_ID 6 7gr

Recipe_ID 3 = Ingredient_ID 4 10 gr

My result should look like this for order_ID 1

• -Ingredient_ID 4 6

• -Ingredient_Name a b

• -Recipe_Ingredient_Gr 15 gr 7 gr

• -Recipe_Ingredient_Piece 0 0

• -Unit_Price 2 3

• -Range_piece 200 200

• -Total Price 2x200x15 200x3x7

   SELECT        Orders.Range_Piece, Orders.Order_ID, Stock.Ingredient_Name, Stock.ID, Ingredients.Recipe_Ingredient_Piece, Ingredients.Recipe_Ingredient_Gr, Recipes.Recipes_ID FROM            Ingredients INNER JOIN
                    Recipes ON Ingredients.Recipe_ID = Recipes.Recipes_ID INNER JOIN
                    Stock ON Ingredients.Stock_ID = Stock.ID CROSS JOIN
                    Orders
                    Where Order_ID=22
  

This query will give me this

i dont want to see malzeme 2 malzeme 2 i want to see there just 3 Malzeme like total malzeme 1 total malzeme 2 total malzeme 3

Answer

The more columns you include in the SELECT statement, the more columns you need to add to the GROUP clause (and the groupings have to make sense). It’s not clear how the price of the ingredients for an order are computed. You mentioned Range_Piece 200 200 and on the next line show Total Price 2x200x15 200x3x7 yet on your query results Range_Piece is 7500 for all rows and I have no idea how that column, whatever that is but is related to an order and not an ingredient, figures into a price calculation. So I am just guessing as far as the price calculation:

SELECT Orders.Order_ID,
       Stock.ID,
       Stock.Ingredient_Name,
       SUM(Ingredients.Recipe_Ingredient_Gr) AS Total_Grams,
       SUM(Orders.Range_Piece * Stock.Unit_Price * Ingredients.Recipe_Ingredient_Gr) AS Total_Price
FROM Orders INNER JOIN Recipes_Orders ON Orders.Order_ID = Recipes_Orders.Order_ID
INNER JOIN Recipes ON Recipes_Orders.Recipe_ID = Recipes.Recipes_ID
INNER JOIN Ingredients ON Recipes.Recipes_ID = Ingredients.Recipe_ID
INNER JOIN Stock ON Ingredients.Stock_ID = Stock.ID
Where Orders.Order_ID=22
GROUP BY Orders.Order_ID, Stock.ID, Stock.Ingredient_Name