0

I am trying to SUM values generated from a CASE/WHEN statement. The only problem is that the values come from different fields.

Example:
enter image description here
This is a set of overhead values from one item out of many in my query. In this example, I need to SUM the overhead values 14.850000 and 1.166600 to 16.016.
The vac_type column determines the type of costs. In this case, when vac_type = WO1 or WO2, they are overhead costs. W10 is not, thus the NULL value.

In all, there are 12 vac_types that are overhead values. They are all listed in my CASE/WHEN statement. I need to SUM all of these overhead values by item_no, but since the vac_type field has two different values, my query considers them DISTINCT from each other and unable to be summed.

This is my unsuccessful attempt to SUM Overhead by item_no. It generates the list in which the screenshot above comes from: 

SELECT DISTINCT 'Overhead' =  SUM(CASE
                          WHEN vac_type IN('A01', 'AD0', 'ADX', 'A01', 'CI0', 'DO1', 'DP9',
                          'O20', 'PWO', 'TO1', 'WO1', 'WO2')
                     THEN (Cost_Values)
                     ELSE NULL
                     END,
         item_no,
         comp_item,
         vac_type

FROM     table_1

GROUP BY item_no,
         comp_item,
         vac_type
Jabo13
  • 65
  • 1
  • 1
  • 7
  • Edit: I cannot contain vac_type in a WHERE statement because after I finish overhead costs, I have to write another CASE/WHEN statement to cover other types of costs. – Jabo13 Jun 16 '16 at 17:34
  • 1
    you just need to remove the `vac_type` column from the `SELECT` list and the `GROUP BY`. Also, no need for the `DISTINCT` – Lamak Jun 16 '16 at 17:36
  • 2
    if you need to sum by item no then remove comp_item and vac_type from select and group by, also instead of using "ELSE null" use ELSE 0. – Kostya Jun 16 '16 at 17:37
  • 2
    Which DBMS are you using? `'Overhead' = SUM()` is invalid standard SQL –  Jun 16 '16 at 17:43
  • @Lamak thank you, I didn't realize that vac_type did not have to be in the SELECT list to be used as a filter that worked! – Jabo13 Jun 16 '16 at 17:46

1 Answers1

0

Thank you to @Lamak for answering. In order to SUM overhead by item_no, any unique fields need to be removed from the SELECT and GROUP BY statement. By removing vac_type, I got the data I needed.

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
Jabo13
  • 65
  • 1
  • 1
  • 7
  • Which DBMS allows you to compare a varchar against a number like that? `'Overhead' = sum(...)` and what is that supposed to do? A comparison `'Overhead' = 42` doesn't make sense –  Jun 16 '16 at 20:09