RE: MIDRANGE-L Digest, Vol 2, Issue 1440

["Smith, Dave" <DSmith@xxxxxxxxx>]

Elvis:  the Float works graet....thank you!!!!!
Eric: Have not tried DEC yet but will tonight after I put kids to bed.   Thanks 
for the reply!!!!
Rob:  Thanks for running the test !!!!!!
 
What happens if my divisor SUM() value is 0.  Do I get an error or will SQL 
automagically make the value in my result set 0?

                from: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
                subject: RE: SQL  dividing the sum of two results on a select
                
                Dave,
                
                AFAIK, SQL performs integer division, meaning it rounds to the 
nearest
                integer value.
                If your first sum is smaller than the second one, you'll always 
get a result
                of zero.
                
                So, check your sum values independently and make sure that's 
not the case.
                Another thing you can try is casting the values within the sum 
function to
                floats, i.e. SUM(FLOAT(field1)).  Division should then return 
the real
                division (with decimal points and such).
                
                Elvis
                
                from: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
                subject: RE: SQL  dividing the sum of two results on a select
                
                Dave,
                
                You are getting whacked by the decimal precision rules in place 
for
                expressions.  Whenever calculating an expressions, the rule is 
to not lose
                "whole digits", or anything to the left of the decimal point.  
Since you're
                calculating a percentage, you need to force the result to be of 
a size that
                will hold the result. 
                
                SELECT                       
                              TSSLS#,
                               IDGRP,
                              SUM(TSSLS$),              
                              SUM(TSCOMD),
                              dec(SUM(TSCOMD)/SUM(TSSLS$),20,10)
                 FROM LIB.SLPTS
                 JOIN LIB.INDESCRP  ON IDS# = TSPRD and IDSF = 'A'
                 WHERE IDGRP = 957 AND   TSSTS <> 'D'
                GROUP BY IDGRP, TSSLS#
                ORDER BY IDGRP, TSSLS#
                
                There's a little about assignment rules here:
                
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/db2/rbafzmst47.htm
                
                
                Eric DeLong
                Sally Beauty Company
                MIS-Project Manager (BSG)
                940-898-7863 or ext. 1863
                
                
                from: rob@xxxxxxxxx
                subject: RE: SQL  dividing the sum of two results on a select
                
                I thought the same thing until I tried
                
                SELECT NBRA, NBRB, NBRC, NBRD, nbra/nbrb, nbrc/nbrd
                from rob/davesmith
                
                
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+.
                NBRA   NBRB   NBRC   NBRD                        NBRA / NBRB    
      NBRC
                / NBRD
                   1      2   3.00   4.00      .5000000000000000000000000000
                .7500000000000000000000000000
                
                But this gives me:
                
                SELECT NBRA, (sum(nbra)/sum(nbrb))
                from rob/davesmith
                group by nbra
                order by nbra
                
                ....+....1....+....2....+....3....+....4....+....
                NBRA                          Numeric Expression
                   1                                           0
                
                However your casting was a good idea:
                SELECT NBRA, (float(sum(nbra))/float(sum(nbrb)))
                from rob/davesmith
                group by nbra
                order by nbra
                ....+....1....+....2....+....3.
                NBRA         Numeric Expression
                   1    5.0000000000000000E-001
                
                Rob Berendt