How to calculate percentage

[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

[slam]

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;

[trev]

Thanks That worked great
Trev

[trev]

Slam,
What if you added a date variable into the mix.
Like between date1 and date2

[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

[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]

[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

[mber]

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



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

[trev]

Nope, I have 8.1.5
Thanks anyway

[slam]

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.

[trev]

Thanks everyone for all your help.
That worked great Slam!!!

Trev