msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Cool SQL Analytic Function: RATIO_TO_REPORT

RATIO_TO_REPORT is a very handy and useful function. Before going through what it does, let’s first look at this example:

SELECT   last_name, salary,
         ROUND (salary / SUM (salary) OVER () * 100,
                2
               ) percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name;

Returns:

        LAST_NAME           SALARY              PERCENT_OF_TOTAL            
------------------------- ---------- -------------------------------------- 
Baida                           2900                                  20.86 
Colmenares                      2500                                  17.99 
Himuro                          2600                                  18.71 
Khoo                            3100                                   22.3 
Tobias                          2800                                  20.14 

5 row(s) retrieved

The percent_of_total column represents the salary for each employee as a percentage of the total salary paid to all clerks. The above query can also be re-written using the RATIO_TO_REPORT SQL function:

SELECT   last_name, salary,
         ROUND
            (ratio_to_report (salary) OVER () * 100,
             2
            ) AS percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name

Both queries return the same output.

Oracle documentation definition: The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. Its syntax is:

RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

In this, the following applies:

  • expr can be any valid expression involving column references or aggregates.

  • The PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is to be computed. If the PARTITION BY clause is absent, then the function is computed over the whole query result set.

Finally, Tom Kyte used the RATIO_TO_REPORT function to calculate the percentage of each browser hitting his blog and to calculate the percentage of his posts per month. Sergio Leunissen used the RATIO_TO_REPORT function as a basis for an APEX (HTML DB) report.

SQL Analytic functions are simple and powerful.


Filed in Oracle, Tips on 14 Jun 06 | Tags: , ,


Reader's Comments

  1. |

    Hi Eddie; used it today the first time! Karl

  2. |

    Using the query below, I need to calculate ratio of paper to electronic claims. Pls help

    select practice_fk as ‘Prac_No1′, p.doc_short_descr as ‘Prac_Name1′, DS.dsp_ind, accreditation, accred_level, SUM(isnull(convert(numeric,(1)),0)) as ‘Total_Claims’, SUM(CASE WHEN convert(numeric,trade_fk) 0 THEN 1 ELSE 0 END) as ‘EDI’, SUM(CASE WHEN convert(numeric,trade_fk) = 0 THEN 1 ELSE 0 END) as ‘Paper’

    from ( select c.claim_fk,c.trade_fk,c.practice_fk,c.rcvd_date from tf_claim C with (nolock) where benefit_amt >= 0.05 AND practice_type IN (14,15,50,51) AND convert(datetime,convert(varchar,claim_dt),112) BETWEEN ’1 MAY 2008′ AND ’31 AUG 2008′ group by c.claim_fk,c.trade_fk,c.practice_fk,c.rcvd_date ) CLAIM LEFT JOIN td_practice P with (nolock) ON (practice_pk = practice_fk) LEFT JOIN pcdw2.dw_staging.dbo.tsd_dsp ds ON (ds.practice_pk = claim.practice_fk) LEFT JOIN dbo.td_negotiation2 n on (n.negotiation_pk = ds.neg_num)

    Where DS.dsp_ind = ‘DSP’

    group by practice_fk, p.doc_short_descr,ds.dsp_ind,accreditation, accred_level

  3. |

    Hi Eddie,

    I googling for ratio_to_report and came across your post. I noticed it links to an old blog post of mine. orablogs.com kicked the bucket a long time ago. FWIW, I revived this post on my new blog:

    http://blogs.oracle.com/sergio/2008/10/quick_and_dirty_inline_charts_1.html

    Sergio

  4. |

    Thanks Sergio, I have updated my post with your new URL.