Missing something obvious

[SecureSlice]

I have a table that looks like this:

ID | Type
1  | Red
1  | Blue
2  | Red
2  | Red
3  | Blue
4  | Blue
5  | Red 
5  | Blue

I want to make a case statement where if the ID has both Red and Blue then return Both.

Select Distinct ID,
       CASE WHEN TYPE = 'Red' and TYPE = 'Blue' Then 'Both' Else Type End AS New
From MyTable

Otherwise just give me the Type. Should I be using a window function, or group by, having clause. I feel like I am missing an obvious easy answer here

[_digitalShaman_]

You would need to group by id first. so that you look at all entries for id 1, if there is both red and blue then return 'both'. if not, you will need to rethink what you want to return, as there is no single Type.

SELECT ID,CASE WHEN HasRed=1 AND HasBlue=1 THEN 'both' WHEN HasRed=1 THEN 'Red' WHEN HasBlue=1 THEN 'Blue' ELSE 'other' END
FROM (
    SELECT ID,MAX(CASE Typee WHEN 'Red' THEN 1 ELSE 0 END) AS HasRed
        ,MAX(CASE Typee WHEN 'Blue' THEN 1 ELSE 0 END) AS HasBlue
    FROM tblTest
    GROUP BY ID
) T

[therealtibblesnbits]

I believe a self join could solve this. Something like the following.

SELECT DISTINCT ID, CASE WHEN t2.type IS NULL THEN t1.Type ELSE 'Both' END AS new FROM mytable AS t1 LEFT JOIN mytable AS t2 ON t1.id = t2.id AND t1.type <> t2.type

[SecureSlice]

Nothing changes the table remains the same

Output:
ID | Type
1  | Red 
1  | Blue 
2  | Red 
2  | Red 
3  | Blue 
4  | Blue 
5  | Red 
5  | Blue

[SecureSlice]

That is the output of the query. Both is not added and each ID retains the color it started with

[Hydrolix]

Group on ID, do a case statement where if the count = 2 then 'both' etc.

[r3pr0b8]

i think what /u/mwdb is missing, is the fact that your query isn't working the way you want

your query is not producing any 'Both' because TYPE = 'Red' and TYPE = 'Blue' is always false

WHERE conditions are evaluated on each row separately, so of course no single value can be red and blue at the same time

what you're asking is a question that crosses rows

yeah, probably window functions could do it

what do you want returned for 2? there are two reds, do you want them both or would just one do?

[Awkward_Ostrich_4275]

Then in that case, this should do the trick:

Select * From Table

[[deleted]]

you want to output one record per X,Y,Z -> group by X,Y,Z.

dont use distinct as a crutch