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 -----Original Message----- From: Smith, Dave [mailto:DSmith@xxxxxxxxx] Sent: Thursday, September 18, 2003 12:23 PM To: midrange-l@xxxxxxxxxxxx Subject: SQL dividing the sum of two results on a select Could anybody tell me why (SUM(TSCOMD)/SUM(TSSLS$)) always returns 0 in the following select statement: V5R2, The two SUM() fields never equal 0 yet the result of the division comes back as 0????????? SELECT TSSLS#, IDGRP, SUM(TSSLS$), SUM(TSCOMD), (SUM(TSCOMD)/SUM(TSSLS$)) 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# _______________________________________________ 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.
This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact