# How to calculate percentage

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 10-10-2000, 04:43 PM
trev
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
• 10-11-2000, 06:29 AM
slam
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;
• 10-11-2000, 10:06 AM
trev
Thanks That worked great
Trev
• 10-11-2000, 10:45 AM
trev
Slam,
What if you added a date variable into the mix.
Like between date1 and date2
• 10-11-2000, 01:11 PM
trev
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
• 10-11-2000, 02:46 PM
mber
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]
• 10-11-2000, 04:42 PM
trev
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

• 10-11-2000, 04:53 PM
mber
Quote:

Originally posted by trev
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

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

• 10-11-2000, 05:00 PM
trev
Nope, I have 8.1.5
Thanks anyway
• 10-12-2000, 05:21 AM
slam
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.
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last