SQL - SUMMING CASE/WHEN Continued

Question

I just asked a question linked here about Summing from a CASE/WHEN query. Part of my question was answered, which leads to another question.

See picture below:

I have three fields, item_no, comp_item_no and Overhead. The comp_item_no is a list of component items for the item_no field. This means that items 00011PP, 00011PPX and 00011X, are at least partially created from item 00011.

Items 00011 and 00011S are made up from item ST1FJP91 which has a NULL overhead cost.

Unfortunately, the Overhead value displayed in the field does not take component items into consideration. Instead of the $23.44 displayed for item_no 00011PP, the true Overhead value would be $23.44 + $16.02, or $39.46. That is because item_no 00011 was at least partially used in creating item_no 00011PP and it's Overhead value needs to reflect that.

I need to find a way to SUM these overhead values to include their respective component items, but I cannot find a feasible solution. Here is my query showing the rest of the story:

SELECT 'Overhead' = Sum(CASE
                      WHEN vac_type IN ( 'A01', 'AD0', 'ADX', 'AO1',
                                                      'CI0', 'DO1', 'DP9', 'O20',
                                                      'PWO', 'TO1', 'WO1', 'WO2' ) 
THEN ( Cost_values)
                      ELSE NULL
                    END),
   item_no,
   comp_item_no
FROM   Table_1       
GROUP BY item_no,
         comp_item_no

Just so you know, vac_type is an identifier for different Values, meaning when vac_type IN ( 'A01', 'AD0', 'ADX', 'AO1', 'CI0', 'DO1', 'DP9', 'O20',PWO', 'TO1', 'WO1', 'WO2' ), then the Values are Overhead

jeez, you just keep adding more and more details to your question. Can't you just use `comp_item_no` in the `SELECT` and remove `item_no` from it and the `GROUP BY`? — Lamak, Jun 16 '16 at 19:46
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Including an image with truncated column names and values doesn't help. — HABO, Jun 17 '16 at 03:05

score 1 · Answer 1 · answered Jun 16 '16 at 19:51

If I understand correctly, you need to sum the overhead for each item. Then you need to add in the overhead for the related items. If so:

with toh as (
      select t1.item_no, t1.comp_item_no,
             (sum(case when vac_type in ( . . . ) then overhead else 0 end) as overhead
      from table_1 t1
     )
select toh.item_no, toh.comp_item_no, 
       (toh.overhead + sum(toho.overhead)) as overhead
from toh left join
     toh toho
     on toh.comp_item_no = toho.item_no
group by toh.item_no, toh.comp_item_no, toh.overhead;

SQL - SUMMING CASE/WHEN Continued

1 Answers1