Midwire Midwire - 3 months ago 14
SQL Question

Howto select multiple fields and aggregate (or rollup) on one

I need to create a report across several joined tables as follows:

SELECT
r.recipe_id "Recipe ID"
,r.name "Recipe Name"
,m.name "Meal Name"
,array_agg(distinct(bucket.name)) as "Plans"
,mn_sodium.meas_amt "Recipe Sodium"
,mn_cholesterol.meas_amt "Recipe Cholesterol"

FROM meals m

-- Join Recipes
INNER JOIN meals_recipe_xref mrx
ON mrx.meal_id = m.meal_id
INNER JOIN recipes r
ON r.recipe_id = mrx.recipe_id

-- Determine Plans
LEFT OUTER JOIN plans dmo
ON dmo.meal_id = mrx.meal_id
LEFT OUTER JOIN calorie_buckets bucket
ON bucket.bucket_id = dmo.bucket_id

-- Get sodium and cholesterol
LEFT OUTER JOIN recipe_nutrients mn_sodium
ON mn_sodium.recipe_id = r.recipe_id
AND mn_sodium.nutri_type_id = 64 -- sodium
LEFT OUTER JOIN recipe_nutrients mn_cholesterol
ON mn_cholesterol.recipe_id = r.recipe_id
AND mn_cholesterol.nutri_type_id = 26 -- cholesterol

GROUP BY
r.name
,m.name
,r.recipe_id
,mn_sodium.meas_amt
,mn_cholesterol.meas_amt

ORDER BY m.name


...which gives me sodium and cholesterol amounts for each recipe in a given meal (a meal is comprised of potentially multiple recipes).

What is the best way to rollup those sodium and cholesterol values for each meal and duplicate those in the report?

I've tried to use
GROUPING SETS
, but no joy. Any help is appreciated.

enter image description here

Answer

From what I understand, you want the sum of values of mn_sodium.meas_amt and mn_cholesterol.meas_amt per m.name. As you want the same resulting values in a set of rows partitioned by m.name, you can simple use window functions, being m.name the PARTITION BY:

SELECT
  r.recipe_id "Recipe ID"
  ,r.name "Recipe Name"
  ,m.name "Meal Name"
  ,array_agg(distinct(bucket.name)) as "Plans"
  ,mn_sodium.meas_amt "Recipe Sodium"
  ,mn_cholesterol.meas_amt "Recipe Cholesterol"
  ,sum(mn_sodium.meas_amt) OVER(PARTITION BY m.name) AS "Meal Sodium"
  ,sum(mn_cholesterol.meas_amt) OVER(PARTITION BY m.name) "Meal Cholesterol"
FROM
...

Another syntax you could use is naming the window apart for reuse:

SELECT
  r.recipe_id "Recipe ID"
  ,r.name "Recipe Name"
  ,m.name "Meal Name"
  ,array_agg(distinct(bucket.name)) as "Plans"
  ,mn_sodium.meas_amt "Recipe Sodium"
  ,mn_cholesterol.meas_amt "Recipe Cholesterol"
  ,sum(mn_sodium.meas_amt) OVER w_meal AS "Meal Sodium"
  ,sum(mn_cholesterol.meas_amt) OVER w_meal "Meal Cholesterol"
FROM
...
WINDOW w_meal AS (PARTITION BY m.name)
Comments