Home » SQL & PL/SQL » SQL & PL/SQL » %age use using COUNT()
%age use using COUNT() [message #264283] Sun, 02 September 2007 17:37 Go to next message
Gary Revell
Messages: 6
Registered: August 2007
Junior Member
Hi all,

I want to display the % of each object type count against the total number of objects. I came up with this....

with q_aocnt as
(
select count(*) as ao_cnt from all_objects
),
q_ao_by_type as
(
select ao.object_type as obj,
count(ao.object_type) as obj_cnt
from all_objects ao
group by ao.object_type
)
select obj,
obj_cnt,
to_char(obj_cnt/ao_cnt*100,'99.999') as Percent
from q_aocnt,
q_ao_by_type
order by 3 DESC

Which works, but is a little over complicated, and may well cause a full table scan to get the object count() for each division. I looked at the analytics etc but was unable to find anything. Can someone do this more succinctly?

Thanks!

Gary
Re: %age use using COUNT() [message #264291 is a reply to message #264283] Sun, 02 September 2007 21:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The easiest way is probably RATIO_TO_REPORT

Ross Leishman
Re: %age use using COUNT() [message #264319 is a reply to message #264283] Mon, 03 September 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: %age use using COUNT() [message #264368 is a reply to message #264283] Mon, 03 September 2007 02:25 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
This is a nice function
Quote:

The easiest way is probably RATIO_TO_REPORT



Using this function i get the output
select object_type,count(object_type) obj_cnt,
to_char((ratio_to_report(count(object_type)) over ())*100,'99.999') as percents
from  all_objects
group by object_type
Re: %age use using COUNT() [message #264411 is a reply to message #264368] Mon, 03 September 2007 04:08 Go to previous message
Gary Revell
Messages: 6
Registered: August 2007
Junior Member
Hi,

Many thanks for your input, I guessed there must have been an analytic but wasn't sure which one.

Best regards

Gary
Previous Topic: Req Help in pl/sql query
Next Topic: Lock
Goto Forum:
  


Current Time: Thu Dec 08 12:24:42 CST 2016

Total time taken to generate the page: 0.12213 seconds