Oracle Analytic Function RATIO_TO_REPORT
 This is an example of Oracle analytic function RATIO_TO_REPORT to analyze monthly sales as a percentage (ratio) of annual sales. The monthly to annual sales ratio is computed as Jan/(Sum of 12 months), Feb/(Sum of 12 months) etc. For January it is 11000/183500 = 0.0599, February it is 12000/183500 = 0.0654 etc. as shown in the query output . The next example shows analysis of quarterly sales as a percentage of annual sales ( quarter/annual ratio ). Based on type of analysis being performed, the PARTITION BY clause can be used to group the data ( mixed year data ). If it is blank or null, the function is computed for the complete result set of the query. Note: The NTILE(4) splits the year into four quarters evenly since there are twelve months. If partial year data is available (current year analysis with less than 12 months), then use the syntax shown in the quarter query to get exact quarter for the month/year combination. RATIO_TO_REPORT ```SELECT TO_NUMBER(SUBSTR(q_dw.year_id,5)) month_No, NTILE(4) OVER(ORDER BY TO_NUMBER(SUBSTR(q_dw.year_id,5))) quarter, SUM(sales) monthly_sales, SUM(SUM(sales)) OVER(ORDER BY TO_NUMBER(SUBSTR(q_dw.year_id,5))) ytd_sales, SUM(SUM(sales)) OVER() yearly_total, ROUND(RATIO_TO_REPORT(SUM(sales)) OVER(),6)*100 "MONTHLY_%" FROM (SELECT 201201 year_id, 11000 sales FROM DUAL UNION SELECT 201202 year_id, 12000 sales FROM DUAL UNION SELECT 201203 year_id, 14000 sales FROM DUAL UNION SELECT 201204 year_id, 16000 sales FROM DUAL UNION SELECT 201205 year_id, 15000 sales FROM DUAL UNION SELECT 201206 year_id, 18000 sales FROM DUAL UNION SELECT 201207 year_id, 19500 sales FROM DUAL UNION SELECT 201208 year_id, 17000 sales FROM DUAL UNION SELECT 201209 year_id, 16000 sales FROM DUAL UNION SELECT 201210 year_id, 16500 sales FROM DUAL UNION SELECT 201211 year_id, 13000 sales FROM DUAL UNION SELECT 201212 year_id, 15500 sales FROM DUAL ) q_dw GROUP BY TO_NUMBER(SUBSTR(q_dw.year_id,5)); Monthly Sales Analysis (month/year) -> Output ```

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

1266