DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to calculate percentage

  1. #1
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    If I have a column with three different inputed variables, can I use sql to give me a percentage for that column.Here is an example.

    column
    __________

    red
    red
    red
    blue
    blue
    orange

    sql output >

    column column(percentage)
    ________ _________________

    red 50%
    blue 30%
    orange 20%



    Thanks for any help!
    Trev

  2. #2
    Join Date
    Oct 2000
    Posts
    9

    Exclamation Work around

    There is now such function available in SQL. I have written some PL/SQL code that should do the work.

    SELECT a.color, ROUND(COUNT(a.color)/b.total_rows*100, 2)
    FROM COLORS a, (SELECT COUNT(ROWID) total_rows FROM COLORS) b
    GROUP BY a.color, b.total_rows;

  3. #3
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    Thanks That worked great
    Trev

  4. #4
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    Slam,
    What if you added a date variable into the mix.
    Like between date1 and date2

  5. #5
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    Here is what I came up with but it seems to make the calculations with total_rows = 5 as opposed to 4 that is the result of the date argument.

    1 select a.complaint_cause, round(count(a.complaint_cause)/b.total_rows*100,2) as total_rows
    2 from complaints a, (select count(ROWID)total_rows from complaints where date_entered
    3 between '01-October-00' and '15-October-00')b
    4* group by a.complaint_cause, b.total_rows
    SQL> /

    COMPLAINT_CAUSE TOTAL_ROWS
    ------------------------- ----------
    Billing 75
    Network 25
    Provisioning 25

  6. #6
    Join Date
    Oct 2000
    Posts
    123

    Smile

    If U are using ORACLE 8i, then only need the following:

    SQL> select a, trunc( ratio_to_report(count(a)) over() * 100, 2) as percentage
    from test
    group by a;

    A PERCENTAGE
    ------------ ----------
    black 16.66
    blue 33.33
    red 50

    And finally put where condition before the group thing, let me know if it does work for U.



    [Edited by mber on 10-11-2000 at 04:31 PM]

  7. #7
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    Im getting this error message



    1 select a.color, trunc( ratio_to_report(count(a.color)) over() * 100, 2) as percentage
    2 from test
    3* group by a.color
    4 /
    select a.color, trunc( ratio_to_report(count(a.color)) over() * 100, 2) as percentage
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis


  8. #8
    Join Date
    Oct 2000
    Posts
    123

    Smile

    [QUOTE][i]Originally posted by trev [/i]
    [B]Im getting this error message



    1 select a.color, trunc( ratio_to_report(count(a.color)) over() * 100, 2) as percentage
    2 from test
    3* group by a.color
    4 /
    select a.color, trunc( ratio_to_report(count(a.color)) over() * 100, 2) as percentage
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

    [/B][/QUOTE]

    Answer:

    line2 should be "test a", mine just works.
    Make sure it is the right version of database;

    Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production


  9. #9
    Join Date
    Apr 2000
    Location
    STERLING
    Posts
    7
    Nope, I have 8.1.5
    Thanks anyway

  10. #10
    Join Date
    Oct 2000
    Posts
    9

    Extra where clause is needed

    Trev,

    If you narrow your selection to retrieve total_rows, you should also narrow the selection of the complains to that period of time. Then you should come up with correct percentages. So , al you need to do is add an extra where clause.

    SELECT a.complaint_cause,
    ROUND(COUNT(a.complaint_cause)/b.total_rows *100 ,2)
    FROM complaints a,
    (SELECT COUNT(ROWID)total_rows
    FROM complaints
    WHERE date_entered
    BETWEEN '01-October-00'
    AND '15-October-00') b
    WHERE a.date_entered
    BETWEEN '01-October-00'
    AND '15-October-00'
    GROUP BY a.complaint_cause, b.total_rows;

    Let me know if this worked.

    Kind regards,
    SLAM.

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