Re: SQL Conditional SUM()

El Wed, 11 Jan 2017 09:23:20 -0700
Charles Wilt <charles.wilt@xxxxxxxxx> escribió:

On Wed, Jan 11, 2017 at 9:09 AM, Manuel Chaviano <manny@xxxxxxxxxxxx> wrote:

1) First try:

select
case when QUA=1 then sum(AMT) else 0 end A1,
case when QUA=2 then sum(AMT) else 0 end A2
from Q
group by QUA

3....+....4....+....5....+....6....+....7....+....8....+.
A1 A2
102.98 .00
.00 11.32
******** End of data ********

You get two rows. Not so good.

​You've got SUM in the wrong place, if you do it like my original post shows
select
sum(case when QUA=1 then AMT else 0 end) as A1,
sum(case when QUA=2 then AMT else 0 end) as A2
from Q

You only get one row..
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+.
A1
A2
102.98
11.32

Charles​

That works very well too!

I found many different ways while searching.
I tried the "pivot" construct, but it did not work:

select * from ( select * from Q ) X
pivot(AMT for QUA in (1,2))
Token PIVOT was not valid. Valid tokens: FOR USE SKIP WAIT WITH FETC

select * from Q
pivot(AMT for QUA in (1,2))
Keyword FOR not expected. Valid tokens: ) ,.

"Decode" does not seem to work (it is basically a "shorter case")

> select
sum(decode(QUA,1,AMT)) A1,
sum(decode(QUA,2,AMT)) A2
from Q
DECODE in *LIBL type *N not found.

(from http://stackoverflow.com/questions/13579143/how-can-i-pivot-a-table-in-db2)

Not sure I am typing something wrong or it is not available in V7R1.

I found this link on how to do it in a more generic way and not limited
to "sum" using a stored procedure:

http://www.itjungle.com/fhg/fhg042115-story01.html