How to filter query in SQL conditionally?-CodePudding

I have a query that shows me the earnings for each shop_id and country as below.

select shop_id,
       country,
       start_date,
       sum(earnings) as earnings
       
from x
where country IN ('DE', 'IT', 'ES')     
group by 1,2,3

However, I want to have only three shops in the country DE and all shops in the rest of the countries.

shop_id IN ('1', '2', '3')

How can I do this?

CodePudding user response：

It sounds like your criteria could use some nesting, like this:

select shop_id,
       country,
       start_date,
       sum(earnings) as earnings
       
from x
where country IN ('IT', 'ES') 
OR (country = 'DE' AND sop_id IN ('1','2','3'))
group by 1,2,3

CodePudding user response：

You can add a CASE statement in the WHERE clause that checks for DE in the country column and returns only the three shop_id's specified, otherwise return the shop_id.

WHERE
CASE 
    WHEN country = 'DE' AND shop_id IN ('1', '2', '3') THEN 1
    WHEN country IN ('IT', 'ES') THEN 1
    ELSE 0
END = 1