SQL “all in” group by

I’m having troubles with some sql statement.

I have a table like this:

I couldn’t find any SQL form to do that but I want something like this:

SELECT 
   Col1, 
   CASE WHEN all(Col2 in ('A','B','C') THEN "auto"
        ELSE distinct Col2 not in ('A','B','C')        as Col2
FROM mytable
GROUP BY Col1

Obviously the query is not valid SQL, so I explain what I want here: If all registers in a group are A, B or C I want the column value to be “AUTO”, in the other hand, if there is any column value different than A,B or C I want that to be selected.

So the result would have to be:

Col1   Col2
1      AUTO
2      R

Answer

You could use aggregation:

select
    col1,
    coalesce(max(case when Col2 not in ('A', 'B', 'C') then Col2 end), 'AUTO') Col2
from mytable 
group by col1

If all values of Col2 for a given Col1 belong to A/B/C, then max(case when Col2 not in ('A', 'B', 'C') then Col2 end) returns null, which the outer coalesce turns to 'AUTO'. Else, the maximum value other than A/B/C is returned.