SUM() in CASE WHEN statement

[michal2442]

Hi, I must use conditions in my SQL statement. Please tell me: can I use sum() funtion within CASE WHEN clause? Example below.

select x, sum(y) from (
select a.xxx A, a.yyy B, b.xxx C, b.yyy D, 
case (b.zzz)
  when b.yyy > 0 then (a.yyy / a.xxx)
  when b.yyy < 0 then (a.yyy * a.xxx)
  when b.yyy = 0 then sum(a.yyy + sum(100/(100*a.yyy)))     <---- I need to do such thing with sum(), is it possible to make in SQL in DB2?

  end as Var


from table1 a, table2 b where a.xxx = b.yyy
) as temp

where A > 0
group by x

Thanks for help !

[TedHolt]

Re: SUM() in CASE WHEN statement

That syntax doesn't work, Michal. SUM is an aggregate function, not a scalar function, and you don't have a GROUP BY clause.

I'd like to suggest another way, but I don't understand what your query is supposed to do.

[michal2442]

Re: SUM() in CASE WHEN statement

But what if I add GROUP BY? I also won't work?
I was given business specification with these 3 conditions (before there was only one, so statement worked). But with this sum in 3rd condition I don't know how to change the statement to working one.

[B.Hauser]

Re: SUM() in CASE WHEN statement

You need another sub-Select or better a CTE.
Within this Sub-Select the a information is aggregated.
The aggregated Sub-Select or CTE is finally joined to the other tables.

Birgitta

[Michael Jones]

Re: SUM() in CASE WHEN statement

Aggregate functions like SUM, MIN, MAX, etc. can be used in conjunction with CASE WHEN.

When you use aggregate functions, you need to use them in conjunction with an aggregate query at the same query or sub-query level of scope. GROUP BY is one way to structure an aggregate query. Use of SELECT columns that are ALL aggregate function expressions, without GROUP BY, is another way.