CODEWITHSUNDEEP
sqlsql-server
nitin kaushik
nitin kaushik

Reputation: 5

Use a 'in' statement inside a 'case' expression

How can I use IN in my "case expression"?

This is my query where I want count if any match like mumbai found then as mumbai:

 select TYPEOFPRODUCT,
        SUM(CASE(BRANCH) WHEN IN('Mumbai1' OR 'Mumbai2') THEN 1 ELSE 0 END) AS 'Mumbai'
        SUM(CASE(BRANCH) WHEN IN('Delhi1' OR 'DelhiMain') THEN 1 ELSE 0 END) AS 'Delhi'
from los_ext  
group by TYPEOFPRODUCT

Upvotes: 0

Views: 60

Answers (2)

Hart CO
Hart CO

Reputation: 34774

You were close, you just need to comma separate values for IN rather than using OR, and there are two formats for CASE expressions, putting the field with the criteria after WHEN is more flexible, so I prefer to use it exclusively:

SELECT TYPEOFPRODUCT
      ,SUM( CASE WHEN BRANCH IN('Mumbai1','Mumbai2') THEN 1 ELSE 0 END) AS Mumbai
      ,SUM( CASE WHEN BRANCH IN('Delhi1', 'DelhiMain') THEN 1 ELSE 0 END) AS Delhi
FROM los_ext  
GROUP BY TYPEOFPRODUCT

Upvotes: 1

Vipin Jain
Vipin Jain

Reputation: 3756

Its query select sum of individual branchs

select TYPEOFPRODUCT,
SUM(CASE WHEN (BRANCH = 'Mumbai1' OR BRANCH = 'Mumbai2' ) THEN 1 ELSE 0 END) AS 'Mumbai',
SUM(CASE WHEN (BRANCH = 'Delhi1' OR BRANCH = 'DelhiMain' )  THEN 1 ELSE 0 END) AS 'Delhi'
from los_ext  
group by TYPEOFPRODUCT

Upvotes: 0

Related Questions