Skip to Main Content
  • Questions

  • Function or Aggregate for Percent of Total Rows?

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

Thanks for the question, Steve.

Asked: April 27, 2009 - 4:21 pm UTC

Last updated: April 28, 2009 - 9:46 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom,

Seems like I should know this or be able to figure it out easily, but how do I get the percent of total rows represented by each Group By value? I've searched the site without success.

For example:
Select hair_color, count(*), some_func() as pct_of_total
from people
where country = 'USA'
Group by hair_color

I could use something like an inline view with the same Where clause to get the total count and then do the math in a selected column, but I figure there must be a cleaner more efficient approach.

Thanks!

and Tom said...

ops$tkyte%ORA11GR1> select job, count(*),
  2         to_char( ratio_to_report( count(*) ) over ()*100, '999.99') ||' %'
  3    from emp
  4   group by job
  5   order by job;

JOB         COUNT(*) TO_CHAR(R
--------- ---------- ---------
ANALYST            2   14.29 %
CLERK              4   28.57 %
MANAGER            3   21.43 %
PRESIDENT          1    7.14 %
SALESMAN           4   28.57 %

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exactly what I needed!

Steve, April 28, 2009 - 3:07 pm UTC


A fast, concise and accurate answer to my question. Perfect.

Thanks!

Adding another group by

Steve, April 28, 2009 - 3:18 pm UTC


Now my requirements have changed a bit and I need to add a "higher" group by column, then get the percentages based on the sub-total counts of that column. The answer appears to be a Partition By clause for that new column, which in effect makes the ratio_to_report function act more like "ratio_to_partition". Very cool.

select country, job, count(*),
to_char( ratio_to_report( count(*) ) over (Partition By country)*100, '999.99') ||' %'
from emp
group by country, job
order by country, job;


Thanks again!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.