I am writing a query using common table expression in SQL SERVER. The Table is to get a sum and then output a color. For example if the sum is 36 the output should be 'Green'. 12 represents green, 20 represents yellow, 30 represents red. So for example if I add 12+12+12, the sum is 36 and therefore the color 'green' shout be outputted. Or 20+20+12 = 52 so that should output 'Yellow'. Below is my code so far.

WITH tempt(tempID, tempValue, Temp Result)

AS

(

SELECT ID, Value, Result,

CASE

WHEN Value = 10 THEN 12

WHEN Value = 20 THEN 20

WHEN Value = 30 THEN 30

END AS 'Signals'

FROM (Base Table)

)

SELECT ID, Signals

sum(CASE WHEN Signals = 36 THEN 'Green' END)

sum(CASE WHEN Signals = 52 THEN 'Yellow' END)

FROM (Base Table)

group by Signals, ID;

I am pretty much trying to call the Signal column and have values added seperately, not as whole, but I am struggling to do that.