How to select ratio of order from total orders?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to select ratio of order from total orders?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to select ratio of order from total orders?

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 ())

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi DaPi,

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

    Thanks a lot!

    Nir

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A more direct function would be ratio_to_report() ... might be more efficient.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    A more direct function would be ratio_to_report() ... might be more efficient.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width