sql sum round up

Question

Sql newbie here, Im stuck on a problem.

I have a recipe ingredient table, and basically what I want to do is find the price of a recipe rounded up to whole ingredients.

So I wrote this sql.

select recipe_id_label, recipe_name, variation_name, portion_size, menu_id, variation_name,
sum (recipe_order_ingredient_quantity * ingredient_expected_cost)
from mb.weekly_menus_recipes_ingredients
where recipe_id_label = '30943'
and portion_size = '2'
group by recipe_id_label, recipe_name, variation_name, portion_size, menu_id, variation_name

This is the interesting part.

sum (recipe_order_ingredient_quantity * ingredient_expected_cost)

This sql gets the right answer, as long as there are no half ingredients. If for example, you have 0,25 packet of carrots, it will not round that up to 1 pack.

So then I tried this.

sum (ingredient_planned_cost* ceiling (recipe_order_ingredient_quantity))

This did not work either. If there are two rows with half a carrot, that sql will sum each of these rows up to 1 carrot so it will count two whole carrots, instead of two halves (1).

So basically I want my code to be able to understand that half + half is whole, and understand that you need a whole even if you only use a quarter.

Answer 1

Is it what you want

select recipe_id_label, recipe_name, portion_size, menu_id, variation_name, 
  sum(ingcost)
from (
    select recipe_id_label, recipe_name, portion_size, menu_id, 
        variation_name, 
        ceiling(sum(recipe_order_ingredient_quantity)) * ingredient_expected_cost ingcost
    from mb.weekly_menus_recipes_ingredients
    where recipe_id_label = 30943 and portion_size = 2
    group by recipe_id_label, recipe_name, portion_size, menu_id, variation_name, 
       ingridient_id, ingredient_expected_cost
) t
group by recipe_id_label, recipe_name,  portion_size, menu_id, variation_name

EDIT Checked syntax with MySql