assign value from select statement 1

[tran008]

How do I assign the select value 'TotalSum' to the case below?

thanks

SELECT ID,
SUM(when month1 =0 then '0' else Month1 END)+
SUM(when month2 =0 then '0' else Month2 END)
as TotalSum,
(CASE
WHEN TotalSum > 0 and TotalSum < 100000 THEN 1
WHEN TotalSum >= 100000 and TotalSum < 250000 THEN 2
WHEN TotalSum >= 250000 and TotalSum < 500000 THEN 3
ELSE 6
END) as trange
FROM yearsale
GROUP BY ID

 

[sunila7]

Is this what you are looking for?

SELECT  TotalSum, (CASE  
    WHEN TotalSum > 0 and TotalSum < 100000 THEN 1
    WHEN TotalSum >= 100000 and TotalSum < 250000 THEN 2
    WHEN TotalSum >= 250000 and TotalSum < 500000 THEN 3
    ELSE 6
    END) as trange
FROM 
(SELECT ID, 
 SUM(CASE when month1 =0 then '0' else Month1 END)+
 SUM(CASE when month2 =0 then '0' else Month2 END)
 as TotalSum

FROM yearsale
GROUP BY ID) TBL

Sunil