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 - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Dynamic array request/response contai... Suja.Sai CICS 2 Tue Jan 24, 2017 11:37 am
No new posts REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm


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