Re: Using TO_CHAR With Subtraction and AVG Function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 19 Dec 2006 07:52:30 +0100
Message-ID: <45878c2e$0$24717$426a34cc@news.free.fr>

<ITAnnaJones_at_gmail.com> a écrit dans le message de news: 1166504708.311640.279470_at_73g2000cwn.googlegroups.com...
|I am trying to show a list of invoice numbers, with each invoice total
| formatted as currency, then the average of all of the invoices
| formatted as currency, then the diff between the two formatted as
| currency. Below is what I have so far, but I cannot get it to work! Any
| assistance is appreciated.
|
| Thanks!
|

SELECT INV_NUMBER,
TO_CHAR(LINE_UNITS*LINE_PRICE,'$99,999.99') AS "INVOICE TOTAL", TO_CHAR(AVG(LINE_UNITS*LINE_PRICE) over (partition by inv_number), '$99,999.99') AS "AVG INVOICE AMT",
TO_CHAR((LINE_UNITS*LINE_PRICE) - (AVG(LINE_UNITS*LINE_PRICE) over (partition by inv_number)), '$99,999.99') AS "DIFF"
FROM LINE
/

Regards
Michel Cadot Received on Tue Dec 19 2006 - 00:52:30 CST