Dave Jarvis' Repositories

select
  --q.grocery_list_id,
  --q.quantity,
  --q.ingredient_name,
  --ruc.country_iso_code,
  --ruc.qualifier,

  sum(ruc.coefficient * q.quantity) AS kilolitres,
  q.unit_id,
  q.ingredient_name_id
FROM (
  select
    rin.label,
    quantity,
    unit_id,
    ingredient_name_id,
    iu.label
  from
    get_grocery_list(1) s,
    ingredient_unit iu,
    recipe.ingredient_name_vw rin
  where
    iu.id = s.unit_id AND
    rin.id = ingredient_name_id
  ) q,
  recipe.unit_conversion ruc
WHERE
  ruc.unit_id = q.unit_id AND
  ruc.country_iso_code = 'SI'
GROUP BY
  q.ingredient_name_id, q.unit_id
ORDER BY
  ingredient_name_id