sql nested "sum (case when) or" select query

Question

I have a select query where I am trying to sum a value based on multiple criteria, grouped by dimension. The output will be the sum of the time greater than 30, 90, or 365 days based on the category, grouped by category. I can't crack the syntax for this, I hope you can help!

The concept looks something like this:

select
CATEGORY,
sum (case when 
        (CATEGORY = '1' and TIME >30) then sum (TIME - 30) or 
        (CATEGORY = '2' and TIME >90) then sum (TIME - 90) or
        (CATEGORY = '3' and TIME >365) then sum (TIME - 365) else 0 end) as output
from database.table
group by CATEGORY

Thanks in advance!

score 3 · Accepted Answer · answered Jan 10 '20 at 00:10

I suspect the syntax you want is:

select CATEGORY,
       sum(case when CATEGORY = '1' and TIME > 30 then TIME - 30 
                when CATEGORY = '2' and TIME > 90 then TIME - 90
                when CATEGORY = '3' and TIME > 365 then TIME - 365
                else 0
           end) as output
from database.table
group by CATEGORY

sql nested "sum (case when) or" select query

1 Answers1