dynamic SQL with SUM

divya_maddi · New User Joined: 03 Nov 2005 Posts: 33

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

CICS Guy · Posted: Tue Feb 12, 2008 9:01 pm

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?

divya_maddi · New User Joined: 03 Nov 2005 Posts: 33

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

dick scherrer · Posted: Wed Feb 13, 2008 8:35 pm

Hello,

divya_maddi · New User Joined: 03 Nov 2005 Posts: 33

i cannot use COBOL as per the requirement

dick scherrer · Posted: Fri Feb 22, 2008 8:15 pm

Hello,