Add $ And Round 2 Decimal Places Sql

I am looking to format an ouput which is created by a sub-query, this sub-query produces a calculated field which i would like to format as $XX.XX. Sub query: (select avg(retail) f

Solution 1:

You could try this:

'$' || Cast((selectavg(retail) from cars 
where brand = 'FORD'or brand = 'TOYOTA') asdecimal(4,2)) as AVG_BRAND_PRICE_01

If you want more than $XX.XX e.g $XXXXXXX.XX then you will need to set the decimal higher e.g. decimal(9,2)

Example SQL Fiddle: http://www.sqlfiddle.com/#!4/9f684/2/0

Solution 2:

I basically just want to add a $ sign and round the output to two decimal places.

I am using isql plus oracle 11g

It is a simple display setting. You need to set the NUMFORMAT properly.

If all you need is to control the display format on SQL*Plus, then it is better to use the SQL*Plus commands to control the display format rather than applying functions at SQL level.

In SQL*Plus:

SQL>SELECTAVG(sal) FROM emp;

  AVG(SAL)
----------2073.21429SQL>set numformat $9999D99
SQL>SELECTAVG(sal) FROM emp;

 AVG(SAL)
---------
 $2073.21SQL>

If you explicitly ROUND the value to two decimal places, then use the ROUND function:

SQL>set numformat $9999D99
SQL>SELECT ROUND(AVG(sal),2) FROM emp;

ROUND(AVG(SAL),2)
-----------------
         $2073.21SQL>

If you want a specific format for a particular column, then you could use the COLUMN FORMAT command, which would override the SET NUMFORMAT property for that column.

For example,

SQL> set numformat $99999.99SQL> column comm format 99999.99SQL> select sal, comm from emp where rownum <=5;

        SAL      COMM
----------- ---------
    $800.00
   $1600.00    300.00   $1250.00    500.00   $2975.00   $1250.00   1400.00