RATIO_TO_REPORT

RATIO_TO_REPORT

Generate the value of the denominator for performance calculations.

Example:
Divide each salesperson's total sales (SP_SALES) by the total region sales (REGION_SALES) to determine what ratio of the total region sales can be attributed to each salesperson.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  SUM(tot_sales) / SUM(SUM(tot_sales)) OVER (PARTITION BY region_id) percent_of_region
  FROM orders
  WHERE year = 2001
  GROUP BY region_id, salesperson_id;


This can be done by RATIO_TO_REPORT function.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  RATIO_TO_REPORT(SUM(tot_sales)) OVER (PARTITION BY region_id)  sp_ratio
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id;