summing the same field twice 2

[rasayldz]

Hi
i have a table like this:

customer code amount
-------- ---- ------
x 1 5
x 1 10
x 2 20
x 2 15
-----------------

"amount" has different meanings depending on the "code" and i want to get sum of the "amount" for both code=1 and code=2 . Is this possible with one SQL statement?

Thanks in advance,

Rasa

ps: i am on DB/2 for iseries...

 

[dickiebird]

if DB/2 supports it, then use the CASE statement

Dickie Bird (-)))

 

[r937]

[tt]select customer
, sum(case when code=1
then amount
else null end) as code1sum
, sum(case when code=2
then amount
else null end) as code2sum
from yourtable
group
by customer[/tt]

rudy

http://r937.com/

 

[rasayldz]

Thank you very much guys,

it did work

 

[rrrkrishnan]

select distinct code, sum(amount)
from yourtable
group by code;

This is more flexible and avoids hardcoding of code values, useful esp. if column 'code' can have multiple values.

 

[r937]

rrrkrishnan, your query is of course the most general, but it is not necessarily the most flexible

specifically, rasa said "amount" has different meanings depending on the "code"

suppose the query was supposed to accumulate 80% of the amount for code 1 items and 90% of the amount for code 2 items, then my query can flexibly be altered like this:

[tt]select customer
, sum(case when code=1
then amount * 0.8
else null end) as code1sum
, sum(case when code=2
then amount * 0.9
else null end) as code2sum
from yourtable
group
by customer[/tt]

and voila, it's done

whereas yours is not quite so flexible

;-)

rudy