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
 

 

dynamic SQL with SUM

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

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Tue Feb 12, 2008 4:10 pm    Post subject: dynamic SQL with SUM
Reply with quote

I have to group record based on a certain condition and then there is an amount which comes against each group. For each group i should also have the percentage of this amount when compared to total amount.

Example:
123 100.00 6.66
456 900.00 60
789 500.00 33.33

the 6.66, 60 and 33.33 are the percentage of totals 100, 900 and 500 against the total amount of 1500(100+900+500)

I have used group by and sum to get the totals for each set and to group by the ID
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Tue Feb 12, 2008 9:01 pm    Post subject:
Reply with quote

And your question is?
I don't think you can do this with a simple DB2 query, since you are looking for a percentage of the total, that will require two queries and some way of saving the total amount....COBOL?
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Wed Feb 13, 2008 12:08 pm    Post subject:
Reply with quote

yes cics guy... exactly what i am looking for... the first quert i have used gives me the sum total for all record (1500) in this case
the second query gives me the sums for each group which is 100, 900 and 500 in this case.... now what i want is how do i save or use the first sum total to use it in the second query... i am using dynamic SQL(cannot be changed)... hence i want to know if there is any way of storing that value for use in the second query
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 Feb 13, 2008 8:35 pm    Post subject:
Reply with quote

Hello,

Quote:
hence i want to know if there is any way of storing that value for use in the second query
Yes. As suggested, save the value(s) in COBOL code.
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Feb 22, 2008 12:57 pm    Post subject:
Reply with quote

i cannot use COBOL as per the requirement
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: Fri Feb 22, 2008 8:15 pm    Post subject:
Reply with quote

Hello,

Quote:
i cannot use COBOL as per the requirement


What business requirement dictates this?
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 REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm


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