New Function to Play with
Ivan, October 26, 2001 - 4:08 pm UTC
I had no idea about the ratio_to_report function and your example answers my questions.
Thanks again,
Ivan
Analytics are fun
Josh, January 16, 2003 - 3:10 pm UTC
Tom,
How could I use this to give me a simple ratio? For example:
(select count(*) from table where col_a = 'Y')/(select count(*) from table)
January 16, 2003 - 7:58 pm UTC
don't know what you mean.
For Josh.....
A Reader, January 17, 2003 - 7:14 am UTC
I think Josh means, can you use it to get a ratio of:
- values meeting criteria in a table / total rows in table
as a 'one-off' value.
Here's a nasty example which gives the ratio of invalid to all objects from the all_objects table - I'm sure there's a better way:
select the_ratio from (
select status, count(*), ratio_to_report(count(*)) OVER () the_ratio
from all_objects
group by status
) where status != 'VALID'
Regards,
Paul
January 17, 2003 - 9:29 am UTC
ahh, ok, i would do this:
select sum(decode(col_a,'Y',1,0))/count(*) from table;
or more readable:
select sum( (case when col_a = 'Y' then 1 else 0 end) ) / count(*) from table;
Still not getting it.
DanielD, October 24, 2005 - 6:51 pm UTC
Tom,
I see your solution, even looked up Oracle8i documenation for RATIO_TO_REPORT function, but still don't know how to calculate percentage of TOTAL_HOURS against grant total for developer. Here is the code I have so far:
BREAK ON username skip 2
COMPUTE SUM OF TOTAL_HOURS ON username
COLUMN TOTAL_HOURS FOR 999.99
SELECT USERNAME
,PROJECTNAME
,CLIENTNAME
,TASKTIMEOFFNAME
,SUM (TOTAL_HOURS) as TOTAL_HOURS
,DEPARTMENTNAME
FROM rp_v_timesheetdetail
WHERE TO_CHAR(entrydate, 'MM') = TO_CHAR(SYSDATE, 'MM')
GROUP BY username
,projectname
,clientname
,tasktimeoffname
,departmentname;
What I am getting with this sql is:
Usename, First DCR Client1 Cost of Development 1.00 Development
OI/OC Client2 Hosting 11.50 Development
OI/OC Client1 Cost of Development 104.50 Development
******************** -----------
sum 117.00
and I would like to get:
Usename, First DCR Client1 Cost of Development 1.00 0.85 Development
OI/OC Client2 Hosting 11.50 9.83 Development
OI/OC Client1 Cost of Development 104.50 89.32 Development
******************** -----------
sum 117.00
Where values 0.85, 9.83, and 89.32 are being calculated from sum for each user (Username, First - 117.00).
Thank you very much for your time.
October 25, 2005 - 1:29 am UTC
sorry - no create tables, no insert intos.....
but isn't it just ratio to report of your sum()
take your query, call it Q
select q.*, 100*ratio_to_report(total_hours) over ()
from (Q) q
/
Got it now
DanielD, October 25, 2005 - 11:16 am UTC
Tom,
Sorry for not providing enough info. When I did apply your example on my query I got percentage, but for whole report. So I've combined it with your first example and I got query that does what I want:
BREAK ON username skip 2
COMPUTE SUM OF TOTAL_HOURS ON username
COLUMN TOTAL_HOURS FOR 999.99
SELECT username
,projectname
,clientname
,tasktimeoffname
,total_hours
,round(100*(ratio_to_report(percent_hours) over (partition by username)),2) as percent
,departmentname
FROM (SELECT username
,projectname
,clientname
,tasktimeoffname
,SUM (total_hours) as total_hours
,SUM (total_hours) as percent_hours
,departmentname
FROM rp_v_timesheetdetail
WHERE TO_CHAR(entrydate, 'MM') = TO_CHAR(SYSDATE, 'MM')
GROUP BY username
,projectname
,clientname
,tasktimeoffname
,departmentname
);
Thank you!
DanielD
Subash Reddy, June 13, 2006 - 4:43 pm UTC