Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Regarding summing up two columns and find difference

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ajay_udl

New User


Joined: 10 Oct 2006
Posts: 29

PostPosted: Tue Mar 10, 2009 9:04 am    Post subject: Regarding summing up two columns and find difference
Reply with quote

Hi All,

I'm looking for a sql query.

I have columns like TRAN_LID_AM,TRAN_TYPE_CD and EXT_CM_NO.
The column TRAN_TYPE_CD have values 'D' and 'C'.
The EXT_CM_NO represents card-no and we need to sum up all TRAN_LID_AM with TRAN_TYPE_CD as 'D' and again sum up all TRAN_LID_AM with TRAN_TYPE_CD as 'C'. After which we need to output EXT_CM_NO who are having the summed up value of 'D' minus the summed up value of 'C' as positive.

Regards
Ajay.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 10, 2009 9:36 am    Post subject:
Reply with quote

Hello,

In addition to the rules description, it will help if you post some sample columns with values that demonstrate the rules and the output you want from the posted input. Make sure the sample data includes all possibilities and that the sample output corresponds with the input.
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Wed Mar 11, 2009 1:44 am    Post subject:
Reply with quote

Hope this query will give some basis
Code:
SELECT A.S1 - B.S2
FROM
(SELECT SUM(TRAN_LID_AM) AS S1 FROM TBL1 WHERE
TRAN_TYPE_CD='D') AS A,
(SELECT SUM(TRAN_LID_AM) AS S2 FROM TBL2 WHERE
TRAN_TYPE_CD='C') AS B


I could not unserstand what so you mean by:
After which we need to output EXT_CM_NO
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 11, 2009 2:00 am    Post subject:
Reply with quote

Hello,

Yes, sample data and the output from that data would be helpful. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us