How to determine distribution of column value in Oracle?

Question

For example, if I have a column representing the flag Y or N , what percentage is Y and what percentage is N ? I know that I can write a query to find each of these numbers and then calculate the percentage myself, but I believe that this should be straightforward, doing it 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 someone point me in the right direction?

Answer 1

Try the 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 can make it cleaner or more convenient to maintain:

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

Answer 2

This may not be the best way, but I think it should work.

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