CODEWITHSUNDEEP
oracle-databaseplsqlanalytics
ninesided
ninesided

Reputation: 23273

How do I determine the distribution of a column's value in Oracle?

For example, if I have a column that represent a Y or N flag, what percentage is a Y and what percentage is an N? I know I can write a query to find each of these numbers and then calculate the percentage myself, but I figure it should be straight forward doing this in PL/SQL with analytic functions like NTILE. 

SELECT COUNT(1), enabled_flag
FROM widgets
GROUP BY enabled_flag;

Gives me:

COUNT(1)   | enabled_flag
  123124   | Y
  234234   | N

I need:

enabled_flag | percentage
Y            |         34
N            |         56

Can anyone point me in the right direction?

Upvotes: 4

Views: 2197

Answers (2)

John Doyle
John Doyle

Reputation: 7793

Try an analytic function as such:

SELECT round(100*COUNT(1)/sum(count(1)) over (),0), enabled_flag
FROM widgets
GROUP BY enabled_flag;

EDIT... you can also use the RATIO_TO_REPORT function which might make it look cleaner or easier to maintain:

SELECT round(100*RATIO_TO_REPORT(count(1)) over (),0), enabled_flag
FROM widgets
GROUP BY enabled_flag;

Upvotes: 5

Zohaib
Zohaib

Reputation: 7116

It might not be the best way, but I guess it should work.

select count(*) * 100 / (select count(*) from widgets), enabled_flag
from widgets
group by enabled_flag

Upvotes: 1

Related Questions