Search code examples
sqlcasewhere-clausecase-when

SQL Filtering using case when in where clause

I need to exclude the last row in the table below:

dim11   dim12   amount1
NULL    1   200
NULL    2   300
Y   3   100
N   3   100

I want to get the sum of amount1 where dim12 in (1,2,3) but only dim12 =3 having dim 11 = 'Y' not in = 'N'.

SELECT
 SUM(AMOUNT1)
FROM table1
WHERE dim12 in (1,2,3)

Summary: I want to exclude all dim12 = 3 and dim11 <> 'Y'

Current Output: 700
Expected Output: 600

Do I need to use CASE in Where clause? Thank you in advance.

Solution

  • where dim12 in (1,2,3) but only dim12 =3 having dim 11 = 'Y' not in = 'N'.

    This sounds like:

    where dim12 in (1, 2) or (dim12 = 3 and dim11 = 'Y')

    If you wanted a conditional sum -- say because you have other columns as well -- then use:

    select sum(case when dim12 in (1, 2) or dim11 = 'Y' then amount end)
from t
where dim12 in (1, 2, 3)