Home > IT Answers > Business Intelligence > Separate SUM results on a single line
PeterWarren
15 pts.
 Separate SUM results on a single line
DB2, DB2 Views, SUM function, SUM statement
Can DB2 return separate SUMfunction within one statement. If I have : SELECT BRANCH,SUM(AMOUNT) ,COUNT(*) FROM TableA WHERE BRANCH IN (180002,180092) gives

Branch,Amount,Count

180002,+100,3

can I return two SUMs - one for positive values of amount and one for negative amounts so will get :

 Branch, credit, debits, Count

 180002, -100,+200,3



Software/Hardware used:
IBM DB2 v9
ASKED: September 19, 2011  1:41 PM
UPDATED: March 31, 2012  4:02 PM
RELATED QUESTIONS
Answer Wiki:
As mentioned in the discussion below, the subquery needs to be named.
Last Wiki Answer Submitted:  September 20, 2011  1:34 pm  by  philpl1jb   44,630 pts.
All Answer Wiki Contributors:  philpl1jb   44,630 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

You could use the SIGN and DECODE functions, if they are available on your version/platform.

Something like this:

SELECT 
	branch, 
	SUM(DECODE(SIGN(amount),-1,amount,0)) SUM_NEG,
	SUM(DECODE(SIGN(amount),-1,0,amount)) SUM_POS, 
	COUNT(*)
FROM TableA
...

If these functions are not available, you could use a CASE expression.

carlosdl
 63,580 pts.
 

Guys, not sure how to ‘approve’ the answer but I haven’t access to the DECODE but Phil’s CASE suggestion worked 9with one minor correction – I needed to name the subquery).
Many thanks for your help,
Pete.

PeterWarren
 15 pts.