CODEWITHSUNDEEP
sql-servercasewhere-clause
vicky
vicky

Reputation: 279

Using IN clause in THEN portion of a CASE expression

I want to include only certain customergroups for each region in my query. I am using the below query that shows an error.

select sum(salesvalue)
from sales 
where (case when Region = 'TN' then CustomerGroup in (68,75,78,69,51,77,66)
case when Region= 'KL' then CustomerGroup in (77,01,68,51,69,78) end)

How can I achieve this?

Upvotes: 0

Views: 50

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

You can do it without a case expression:

WHERE (Region = 'TN' AND CustomerGroup in (68,75,78,69,51,77,66))
OR    (Region= 'KL'  AND CustomerGroup in (77,01,68,51,69,78))

This produces a simple conditional, and it is arguably easier to read.

Upvotes: 3

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14208

You can't use CASE WHEN END in Where clause.

Just use AND/OR logic like.

If the column = value And the column IN the list of values.

select sum(salesvalue)
from sales 
where (Region = 'TN' AND CustomerGroup in (68,75,78,69,51,77,66))
       OR (Region= 'KL' AND CustomerGroup in (77,01,68,51,69,78))

Upvotes: 1

Related Questions