SQLTeam.com | Weblogs | Forums

Using CASE statement to get a result based on other CASE statement

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.