Question on SUM()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

William Bub - 11 Jun 2006 22:18 GMT

I have the a hypothetical DB2 DETAIL table which includes the following
columns: TRANS_TYPE, TRANS_AMT.
All TRANS_AMTs are positive, but TRANS_TYPE of 1 indicates a credit
(positive), and TRANS_TYPE 2 indicates a debit (negative). I want to write a
query to return the net transaction amount for all transactions. I could get
SUM(TRANS_AMT) WHERE TRANS_TYPE = 1, and then get SUM(- TRANS_AMT) WHERE
TRANS_TYPE = 2, and then add the results. Is there a way to do this with a
single query?

Reply to this Message

Tonkuma - 12 Jun 2006 01:02 GMT

> I have the a hypothetical DB2 DETAIL table which includes the following
> columns: TRANS_TYPE, TRANS_AMT.
[quoted text clipped - 4 lines]
> TRANS_TYPE = 2, and then add the results. Is there a way to do this with a
> single query?

SUM(TRANS_AMT*SIGN(2-TRANS_TYPE*2))

Reply to this Message

Tonkuma - 13 Jun 2006 04:04 GMT

> > I have the a hypothetical DB2 DETAIL table which includes the following
> > columns: TRANS_TYPE, TRANS_AMT.
[quoted text clipped - 5 lines]
> > single query?
> SUM(TRANS_AMT*SIGN(2-TRANS_TYPE*2))

Another expression
SUM(TRANS_AMT*(3-TRANS_TYPE*2))

Reply to this Message

Bob Stearns - 12 Jun 2006 01:15 GMT

Any number of solutions. Here's one:

select who,
sum(case tran_type
        when 1 then tran_amt
        else -tran_amt
    end)as tot
from details
group by who
order by who

Reply to this Message

Brian Tkatch - 13 Jun 2006 16:19 GMT

Yes.

VALUES
(SELECT SUM(TRANS_AMT) FROM Detail WHERE TRANS_TYPE = 1)
- (SELECT SUM(TRANS_AMT) FROM Detail WHERE TRANS_TYPE = 2)

B.

Reply to this Message