Hi,
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,
(CASE
When SUM(Sales) > 5000000 Then 'Group_A'
When SUM(Sales) > 1000000 Then 'Group_B'
Else 'Group_C'
END) As Group_Details,
(CASE
WHEN ('Group_Details') = ('Group_A') then 'Team_A'
WHEN ('Group_Details') = ('Group_B') then 'Team_B'
Else 'Team_C'
End) As Team_Assign
From FSample
Group by Country, Segment
Order by Country, segment;
Result screenshot:
why not avoid the dance and go straight to what you want?
CASE
When SUM(Sales) > 5000000 Then 'Team_A'
When SUM(Sales) > 1000000 Then 'Team_B'
Else 'Team_C'
END) As Team_Assign
1 Like
hi hope this helps
; WITH cte AS
(
SELECT
Country
, Segment
, Sum (Sales) As Total_Sales
FROM
FSample
GROUP BY
Country
, Segment
)
, cte_1 AS
(
SELECT
Country
, Segment
, CASE When Total_Sales > 5000000 Then 'Group_A'
When Total_Sales > 1000000 Then 'Group_B'
ELSE 'Group_C'
END As Group_Details
FROM
cte
)
SELECT
Country
, Segment
, CASE WHEN Group_Details = 'Group_A' then 'Team_A'
WHEN Group_Details = 'Group_B' then 'Team_B'
ELSE 'Team_C'
END As Team_Assign
FROM
cte_1
ORDER BY
Country
, segment
1 Like
@ Yosiasz: Thank you.. this was my initial solution but, I am trying to get the same result with group_details as a reference.
@harishgg1 : Thank you very much, as a beginner, I am trying to understand your script flow.
