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

You mean like this:

SELECT NBRA, 
 CASE 
  WHEN float(sum(nbrb))=0 
    then 0 
  ELSE 
    (float(sum(nbra))/float(sum(nbrb)))
 END 
from rob/davesmith 
group by nbra 
order by nbra 

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 





"Smith, Dave" <DSmith@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/18/2003 03:12 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     <midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        RE: MIDRANGE-L Digest, Vol 2, Issue 1440


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
 

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.