RATIO_TO_REPORT function

The RATIO_TO_REPORT function calculates the fractional ratio of each row to the rest of the rows in the window partition, based on the numeric argument to the function.

Syntax

Notes:

¹ See OVER clause for OLAP window expressions


Element	Description	Restrictions	Syntax
`expression`	Column name, alias, or constant expression	The `expression` must evaluate to a numeric data type. DATE, DATETIME, and INTERVAL columns are not valid. If `expression` references a column, the column must also be in the select list of the Projection clause.	Column Expressions

Usage

This function calculates the ratio of values for a specified numeric column in a row versus the sum of values from all rows in each partition in an OLAP window frame. The name RATIOTOREPORT is a keyword synonym for RATIO_TO_REPORT.

As with all OLAP window aggregation functions, the Window PARTITION, Window ORDER, and Window Frame clauses are optional. The ratios can be applied either to partitioned rows or to the complete query result set. If a window Frame clause is also specified, then the calculated ratio applies to all rows from the current window frame.

The required expression argument must specify a column of a numeric data type, or else be a constant expression that evaluates to a numeric data type. If expression is not a numeric data type, the function fails with Error Message -25862.

If the sum of expression values for all the rows in the current window partition is zero, this function returns the NULL value for each row in that partition.

If another OLAP aggregation function, such as SUM or MAX, has an empty OVER clause, or if the OVER clause contains only a single window PARTITION specification, the result of the OLAP function is the same for every row in the partition. This is not the case, however, for RATIO_TO_REPORT, because different rows in the same window partition are assigned different ratios, totaling 1 (approximately) for each partition. To convert ratios to percentages, multiply the function expression by 100, as in the following example.

Example: RATIO_TO_REPORT function

The following example calculates the decimal fraction of each city's sales, based on all rows that the query returns, as a single report that shows the sales totals for each city, in descending order.

SELECT city, SUM(dollars) AS SALES, 
   RATIO_TO_REPORT(SUM(dollars)) OVER() *100 AS RATIO_DOLLARS
   FROM sales, store, period
   WHERE sales.store_id = store.store_id 
      AND sales.period_id = period.period_id
   GROUP BY city
   ORDER BY sales DESC;

CITY               SALES           RATIO_DOLLARS 
San Jose           896931.15          12.58
Atlanta            514830.00           7.22
Miami              507022.35           7.11
Los Angeles        503493.10           7.06
Phoenix            437863.00           6.14
New Orleans        429637.75           6.03
Cupertino          424215.00           5.95
Boston             421205.75           5.91
Houston            417261.00           5.85
New York           397102.50           5.57
Los Gatos          394086.50           5.53
Philadelphia       392377.75           5.50
Milwaukee          389378.25           5.46
Detroit            305859.75           4.29
Chicago            294982.75           4.14
Hartford           236772.75           3.32
Minneapolis        165330.75           2.32

In the example above, the expression argument to RATIO_TO_REPORT is the numeric aggregate function expression SUM(dollars). The last row of the output indicates that the sales value for the city Minneapolis is approximately 2.32% of the total sales that the query reports.