IleNea IleNea - 3 months ago 18
SQL Question

All the cakes less than group by sum

I have the following sql diagram:

Category:
id_category
category_name

Measurement
id_measurement
name_measurement

Ingredient
id_ingredient
name_ingredient
category_id
measurement_id
nrOfCal

IngredientCake
id_ingredient
cake_code
quantities

Cake
cake_code
cake_name


I don't know how to display all the code and name cakes that have less than 500 calories.
What I've tried:

select [Cake].cake_code , [Cake].cake_name
from [Cake]
inner join [Ingredient_Cake]
on ([Cake].cake_code = [Ingredient_Cake].cake_code)
inner join [Ingredient]
on ([Ingredient_Cake].id_ingredient = [Ingredient].id_ingredient)
where sum([Ingredient].nrOfCal) < 500
group by [Cake].cake_code;


but anyway it gives me the error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


Any solution?

EDIT:

select [Cake].cake_code,
[Cake].cake_name
from [Cake]
inner join [Ingredient_Cake]
on ([Cake].cake_code = [Ingredient_Cake].cake_code)
inner join [Ingredient]
on ([Ingredient_Cake].id_ingredient = [Ingredient].id_ingredient)
group by [Cake].cake_code, [Cake].cake_name
having sum(nrOfCal*quantities) > 500;


I ve edited and now the funny part is that if I let it that way, it returns me how it should, the cakes with the calories lower than 500 but the condition now is (>500), and if I change the condition to < 500, it only returns me the cake that has more than 500 calories. What's going on?

Answer Source

Try to move the aggregate to the having clause:

select     [Cake].cake_code, 
           [Cake].cake_name 
from       [Cake] 
inner join [Ingredient_Cake] 
on         ([Cake].cake_code = [Ingredient_Cake].cake_code)
inner join [Ingredient] 
on         ([Ingredient_Cake].id_ingredient = [Ingredient].id_ingredient)
group by   [Cake].cake_code, [Cake].cake_name
having     sum([Ingredient].nrOfCal) < 500;