# 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....
```

(adsbygoogle = window.adsbygoogle || []).push({});

```
+3

source to share

2 answers

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
```

(adsbygoogle = window.adsbygoogle || []).push({});

```

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'`

.

+3

source

To show only two decimal places, you can use the ROUND function

``````ROUND(AVG(D.PLANAMOUNT), 2) AS AVGPLANAMT
```

(adsbygoogle = window.adsbygoogle || []).push({});

```

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
```

(adsbygoogle = window.adsbygoogle || []).push({});

```

For more information, you can look here .

+1

source

All Articles