Convert decimal to currency in SQL (Oracle)
I am trying to figure out how to get my results to show only two decimal places and currency if possible. Right now I am getting this:
PARTID PARTIDCOUNT AVGPLANAMT
1001 27 17.02148148148148148148148148
1002 13 54.18615384615384615384615384
1003 3 467.4166666666666666666666666
1009 5 862.27
SELECT DISTINCT
D.PARTID,
COUNT(D.PARTID) AS PARTIDCOUNT,
AVG(D.PLANAMOUNT) AS AVGPLANAMT
FROM PARTS....
source to share
How about the following ( SQL Fiddle ):
SELECT PARTID, COUNT(*) AS PARTIDCOUNT,
TO_CHAR(AVG(PLANAMOUNT), 'L999,999,999.00') AS AVGPLANAMT
FROM PARTS
GROUP BY PARTID
For the second parameter I use 'L999,999,999.00'
which will account for any number up to 1 billion. If you need to place a greater number, do something like: 'L999,999,999,999,999.00'
.
source to share
To show only two decimal places, you can use the ROUND function
ROUND(AVG(D.PLANAMOUNT), 2) AS AVGPLANAMT
More information ROUND()
can be found here .
What do you mean by and currency
? Do you just need the currency symbol in front of the $?
If so, try this.
COLUMN AVGPLANAMT FORMAT $990.99
For more information, you can look here .
source to share