Combining MAX and SUM in an Oracle ROLLUP

Question

Here's my SQL:

WITH source1 AS (
   SELECT 'Fruit' foodtype, 'Apple'  food, 20 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Apple'  food, 30 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Grape'  food, 1  weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Carrot' food, 40 weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Leek'   food, 20 weight FROM dual
)
SELECT grouping(food) lv, foodtype, food, max(weight) weight
FROM   source1
GROUP BY foodtype, ROLLUP (food);

The output looks like this:

LV FOODTYPE FOOD   WEIGHT
-- -------- ------ ------
0  Veg      Leek       20
0  Veg      Carrot     40
1  Veg                 40
0  Fruit    Apple      30
0  Fruit    Grape       1
1  Fruit               30

I was expecting it to look like this:

LV FOODTYPE FOOD   WEIGHT
-- -------- ------ ------
0  Veg      Leek       20
0  Veg      Carrot     40
1  Veg                 60
0  Fruit    Apple      30
0  Fruit    Grape       1
1  Fruit               31

In other words, I was expecting the rollup to sum up the maximum weights of each food instead of taking the maximum of all the maximums in the food-type category.

I do have a solution of sorts, but it means having to add an additional layer of SQL-statement nesting:

WITH source1 AS (
   SELECT 'Fruit' foodtype, 'Apple'  food, 20 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Apple'  food, 30 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Grape'  food, 1  weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Carrot' food, 40 weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Leek'   food, 20 weight FROM dual
), source_grp AS (
   SELECT s.foodtype, s.food, max(s.weight) max_weight
   FROM   source1 s
   GROUP BY foodtype, food
)
SELECT grouping(g.food) lv, g.foodtype, g.food, sum(g.max_weight) weight
FROM   source_grp g
GROUP BY g.foodtype, ROLLUP (g.food);

Is there a way to do it without the extra nesting?

Of course, this example is significantly simplified from my real-world situation, which is why I'm trying to find a way to reduce the number of lines of code. Reducing a SQL statement by 60 lines of code significantly eases its maintenance in the long term.

Answer 1

Revisiting this after a few days. It can be done like this:

WITH source1 AS (
   SELECT 'Fruit' foodtype, 'Apple'  food, 20 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Apple'  food, 30 weight FROM dual UNION
   SELECT 'Fruit' foodtype, 'Grape'  food, 1  weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Carrot' food, 40 weight FROM dual UNION
   SELECT 'Veg'   foodtype, 'Leek'   food, 20 weight FROM dual
)
SELECT grouping(s.food) lv, s.foodtype, s.food,
       CASE WHEN grouping(s.food)=1 THEN
          sum(CASE WHEN grouping(s.food)=1 THEN 0 ELSE max(s.weight) END) OVER (PARTITION BY s.foodtype ORDER BY s.food)
       ELSE
          max(s.weight)
       END weight
FROM   source1 s
GROUP BY s.foodtype, ROLLUP (s.food)

To be honest, I'm not 100% sure I like this answer either. Depending on the context and from a maintenance point of view, the CASE-WHEN statement is more difficult to understand than the multi-level SELECT.