Quick and simple frequency analysis
I use this simple query quite often when exploring the data in a table in any Oracle database (from Oracle v8 onwards):
select q.*, 100 * ratio_to_report(c) over () rtr from (select distinct v, count(*) over (partition by v) c from ( select MYCOLUMN v from MYTABLE )) q order by c desc;
Just substitute the table name for “MYTABLE” and the column you’re interested in for “MYCOLUMN”. This gives a frequency analysis of values, e.g.:
V C RTR ======== ====== ============= INACTIVE 401001 92.9254049544 ACTIVE 30529 7.0745950455
V is the value from the column. C is the count of how many times that value appeared. RTR is the % ratio to the total. The first row indicates the most popular value.
If it’s a very large table and you want quicker results, you can run the analysis over a smaller sample easily, just by adding the SAMPLE keyword:
... select MYCOLUMN v from MYTABLE SAMPLE(1) ...
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.