Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help on query with decimal places and trailing 0's

774689Jul 3 2010 — edited Jul 3 2010
Oracle community I'm in need of help in the following situation:

I got the following query:
SELECT (SUM("VALUE1")/COUNT("VALUE2"))
FROM sometable;

this returns the following results:
200
222,5

I want the 222,5 value to be displayed like 222,50 so I tried ROUND(value,2) but then came to the conclusion that round won't show me trailing 0's .
So now I tried to_char(value,'9999DD00') and of course it is returning both 200,00 and 222,50.

Now how do I make it conditional so when a number does not have a decimal place it shows the whole number and when it has a decimal place it shows it in the format to_char(value,'9999DD00') ?

I hope I formulated my situation and question correctly and I hope for a solution.

P.S.
I need it to show up like that since it is an average of product prices

Edited by: Jnijman on 3-jul-2010 13:06
This post has been answered by Hoek on Jul 3 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2010
Added on Jul 3 2010
3 comments
1,369 views