Forum Discussion

Sarah1983's avatar
Sarah1983
Copper Contributor
Mar 21, 2020

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

  • Sarah1983's avatar
    Sarah1983
    Copper 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.

    Sarah1983 

Resources