I am trying to using a CASE statements assign a result which is based on other CASE statement result. But the out come is not as I expected. The query is not throwing any error but the result is not correct. I feel the 2nd CASE statement has some wrong. it shows the same result for all the entries.
Appreciate your help in highlighting the issue here.
SELECT Country, Segment, Sum (Sales) As Total_Sales,
When SUM(Sales) > 5000000 Then 'Group_A'
When SUM(Sales) > 1000000 Then 'Group_B'
END) As Group_Details,
WHEN ('Group_Details') = ('Group_A') then 'Team_A'
WHEN ('Group_Details') = ('Group_B') then 'Team_B'
End) As Team_Assign
Group by Country, Segment
Order by Country, segment;