How to select ratio of order from total orders?

[nir_s]

Hi falks,

I have the following orders table:
SQL> desc orders
Name Null? Type
----------------------------------------- -------- ----------------------------
PROGRAM_NAME VARCHAR2(20)
NUM_OF_ORDERS NUMBER(3)

SQL> select PROGRAM_NAME,NUM_OF_ORDERS
2 from orders;

PROGRAM_NAME NUM_OF_ORDERS
-------------------- -------------
prog1 3
prog2 5
prog3 8
prog4 12

Now,i want to display in one query the result of the ratio of num_of_orders devided in the total of the orders:

PROGRAM_NAME NUM_OF_ORDERS NUM_OF_ORDERS/sum(NUM_OF_ORDERS)
-------------------- ------------- -------------
prog1 3 0.107 (3/28)
prog2 5 0.178 (5/28)
prog3 8 0.285 (8/28)
prog4 12 0.428 (12/28)

Is it possible?

Regards,
Nir

[DaPi]

Have a look at analytic functions here: http://download-west.oracle.com/docs...tion.htm#86772

You'd want something like NUM_OF_ORDERS/(sum(NUM_OF_ORDERS) OVER ())

[nir_s]

Hi DaPi,

I wanted exactly : NUM_OF_ORDERS/(sum(NUM_OF_ORDERS) OVER ())
It works perfect!

Thanks a lot!

Nir

[slimdave]

A more direct function would be ratio_to_report() ... might be more efficient.

[marist89]

Originally posted by slimdave
A more direct function would be ratio_to_report() ... might be more efficient.