Home » SQL & PL/SQL » SQL & PL/SQL » Limiting decimal places to 2
Limiting decimal places to 2 Tue, 31 December 2002 11:09
 Heidi Messages: 37Registered: February 2000 Member
I am summarizing cost amounts across different locations and with the average amount I am getting 20+ decimal places. How can I round to use only two?

----------
SQL Query
----------
SELECT F.facilitycode,
COUNT(ES.estimatenumber) AS NbrofEstimates,
AVG(grandtotal) AS AvgEstCost,
SUM(grandtotal) AS TotalEstCost
FROM estimate ES, facility F
WHERE F.facilityid = ES.facilityid
GROUP BY facilitycode

---------------
Sample Results
---------------
FACILITYCODE NBROFESTIMATES AVGESTCOST TOTALESTCOST
ar.bue.a.icb 8 986.13 7889.04
au.btb.a.pob 130 224.109047619047619047619047619047619048 28237.74
au.mel.a.vcm 40 843.989354838709677419354838709677419355 26163.67
au.syd.a.tyn 48 114.510416666666666666666666666666666667 5496.5
be.anr.a.ace 14 1296.521538461538461538461538461538461538 16854.78
Re: Limiting decimal places to 2 [message #4626 is a reply to message #4623] Tue, 31 December 2002 11:27
 Pete Rigano Messages: 23Registered: December 2002 Junior Member
Use the ROUND function.

SQL> select ROUND(15.192345678, 2) FROM DUAL;

ROUND(15.192345678,2)
---------------------
15.19

The TRUNC number function is similar but just chops the number off to the number of decimals requested. So TRUNC(15.89, 1) would give 15.8 while ROUND(15.89,1) would give 15.9.

pr
 Previous Topic: Join dilemma, too many records returned HELP~ please Next Topic: Date Math
Goto Forum:

Current Time: Fri May 24 06:29:12 CDT 2013

Total time taken to generate the page: 0.22987 seconds