-
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
-
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;
-
Thanks That worked great
Trev
-
Slam,
What if you added a date variable into the mix.
Like between date1 and date2
-
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
-
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]
-
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
-
[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
-
Nope, I have 8.1.5
Thanks anyway
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|