CODEWITHSUNDEEP
sql
James69
James69

Reputation: 239

How to use sum(case) with three conditions

I usually use sum(case) to get sum of some columns:

i.e. SUM(CASE WHEN over05 = 'OK' THEN 1 ELSE 0 END) AS OK_05

and this is perfect when I have a column with two values, but when I have a column where I have three values:

 i.e. over 05 = '1' or 'X' or '2'

how can I do a sum(case)?

Upvotes: 1

Views: 382

Answers (1)

roberto06
roberto06

Reputation: 3864

If you want all three values to return the same thing, you should use IN(): 

SUM(
    CASE
    WHEN over05 IN ('1', 'X', '2') THEN 1
    ELSE 0 END
) AS OK_05

If you want each value to return something different, you should use multiple WHEN ... THEN : 

SUM(
    CASE
    WHEN over05 = '1' THEN 1
    WHEN over05 = 'X' THEN 2
    WHEN over05 = '2' THEN 3
    ELSE 0 END
) AS OK_05

Upvotes: 1

Related Questions