Q:

SQL case both or one or other

Very new to SQL, so excuse my basic question. 
I have a table of IDs & values. Certain values map to one item and other values map to another item. I'm trying to also highlight when IDs have both items, but am running into issues:
SEL 
DISTINCT ID,
CASE 
    WHEN value_id IN (1,2,3,4,5,7,17,18) THEN 'item1' 
    WHEN value_id IN (6,8,9,10,11,12,13,14,15,16) THEN 'item2' 
    WHEN value_id IN (1,2,3,4,5,7,17,18) AND value_id IN (6,8,9,10,11,12,13,14,15,16) THEN 'both' 
    ELSE 0 end AS which_item
FROM table_name
GROUP BY 1, 2

But am not returning "both" when it applies, example ID ABCD has 2 records with values 'item1' and 'item2', but not 1 record with 'both'. I tried making that the first statement in the case, but no luck, either.

A:

You need GROUP BY on just the ID and some conditional logic.  Here is one method:
SELECT ID,
       (CASE WHEN SUM(CASE WHEN value_id IN (1, 2, 3, 4, 5, 7, 17, 18) THEN 1 ELSE 0 END) > 0 AND
                  SUM(CASE WHEN value_id IN (6, 8, 9, 10, 11, 12, 13, 14, 15, 16) THEN 1 ELSE 0 END) > 0
             THEN 'both' 
             WHEN SUM(CASE WHEN value_id IN (1, 2, 3, 4, 5, 7, 17, 18) THEN 1 ELSE 0 END) > 0 
             THEN 'item1'
             WHEN SUM(CASE WHEN value_id IN (6, 8, 9, 10, 11, 12, 13, 14, 15, 16) THEN 1 ELSE 0 END)
             THEN 'item2'
             ELSE 'none'
        END) AS which_item
FROM table_name
GROUP BY 1

