CODEWITHSUNDEEP
sqlsqlite
Beans On Toast
Beans On Toast

Reputation: 1091

Using IN operator with case when in SQL?

I'm trying to run a case when statement that works with the IN operator - so that when the value (or values) URL are provided to the IN clause, it will perform the case when giving the values a 1 or a Null when they match either a URL or a group of URL's that are provided.

I'm rather stuck on how to actually use the IN operator for the case when operation. I want to be able to provide multiple values where it has url - rather than one. Is this possible?

My code is the following: 

create temp table ag_table as 
   with sq AS(select id, business, 
   cat1, cat2, channel, call_reason, 
   COUNT(case cat1 when url then 1 else null end) 
   over (partition by subject 
order by 
time range between 604800 preceding
and current row) as count 
from table_name
where table_name.start >= start and table.end_time <= end
)

To note I am using SQLite

Upvotes: 0

Views: 534

Answers (1)

forpas
forpas

Reputation: 164139

This is the syntax for the CASE expression:

COUNT(case when category1 IN (url1, url2, ...) then 1 end)

There is no need for else null (null is the default value when there is no else).
The same can be achieved by:

SUM(category1 IN (url1, url2, ...))

Upvotes: 1

Related Questions