Forum Discussion
SQL Query problem
Good morning,
I am fairly new to SQL.
For a school project I have created a database with the purpose to calculate the cost of a recipe with products from a grocery store.
My database looks as follows:
I've created the following query:
select a.Recept_ID, a.Recept_Omschrijving,a.Recept_Pers, b.ReceptProduct_ID, c.Product_Omschrijving,b.Recept_Hoeveelheid, d.Verpakking_ID, d.Merk_ID,
d.Verpakking_Omschrijving, d.Vgewicht_Inh, d.VGewichtEenheid, e.Prijs, e.Eenheid, (b.Recept_Hoeveelheid * e.Prijs) as Kostprijs
from Recept as a
inner join ReceptProduct as b
on a.Recept_ID = b.Recept_ID
inner join Product as c
on b.Product_ID=C.Product_ID
inner join VerpakkingProduct as d
on c.Product_ID = d.Product_ID
inner join VerpakkingWinkel as e
on d.Verpakking_ID=e.Verpakking_ID
where a.Recept_ID = 1 and d.Verpakking_ID in (954,1994,2558,2981,3529,3595,4251,4440,4508,5383,5807,6109,6120,6204,6592,6802)
This query will indeed calculate the cost for each ingredient.
But how do I calculate the total cost?
Secondly: I would like to write a query that would give me the lowest price of eah product group.
There are 3 tables that I would have to use:
- Product (has a list with all the product groups)
- ProductVerpakking (has a list of all the possible package for a productgroup (for example Product Spaghetti has different packages)
- VerpakkingWinkel (Has the price of the different packages per store).
How can I get a list of the lowest price per Product Group?
Thank you very much for your help.
Kind regards,
Sarah
- Sarah1983Copper Contributor
I found the solution to calculate the total cost:
select a.Recept_ID, a.Recept_Omschrijving, sum(b.Recept_Hoeveelheid * e.Prijs) as Kostprijsfrom Recept as ainner join ReceptProduct as bon a.Recept_ID = b.Recept_IDinner join Product as con b.Product_ID=C.Product_IDinner join VerpakkingProduct as don c.Product_ID = d.Product_IDinner join VerpakkingWinkel as eon d.Verpakking_ID=e.Verpakking_IDwhere a.Recept_ID = 1 and d.Verpakking_ID in (954,1994,2558,2981,3529,3595,4251,4440,4508,5383,5807,6109,6120,6204,6592,6802)group by a.Recept_ID, a.Recept_Omschrijving
I'm still searching for a solution to get the cheapest products back in the recipe cost query.