All Products
Search
Document Center

RATIO_TO_REPORT

Last Updated: Jun 18, 2021

The RATIO_TO_REPORT function calculates the ratio of a value to the sum of a group of values.

Syntax

RATIO_TO_REPORT(expr) OVER ([query_partition_clause])

Parameter

Parameter

Description

expr

The value can be only a positive constant.

OVER

Uses the OVER clause to define a window for calculation.

Return type

The numeric data is returned.

Examples

The following example shows the ratio of the output of employees to the total output of the department. To create the product table and insert data into the data, execute the following statements:

CREATE TABLE product (name VARCHAR(8), deptno NUMBER, output NUMBER);
INSERT INTO product VALUES('Linda',100,5050);
INSERT INTO product VALUES('Tan',1001,8500);
INSERT INTO product VALUES('Tom',1001,3900);
INSERT INTO product VALUES('John',100,29500);
INSERT INTO product VALUES('Mery',1001,1500);
INSERT INTO product VALUES('Peter',100,1060);
COMMIT;

Execute the following statement:

SELECT name, OUTPUT, deptno, RATIO_TO_REPORT(output) OVER (partition BY deptno) FROM product;

The following query result is returned:

+-------+--------+--------+------------------------------------------------+
| NAME  | OUTPUT | DEPTNO | RATIO_TO_REPORT(OUTPUT)OVER(PARTITIONBYDEPTNO) |
+-------+--------+--------+------------------------------------------------+
| Linda |   5050 |    100 |      .1418140971637180567256388654872226902555 |
| John  |  29500 |    100 |      .8284189834316203313675933726481325470373 |
| Peter |   1060 |    100 |      .0297669194046616119067677618646447627071 |
| Tan   |   8500 |   1001 |      .6115107913669064748201438848920863309353 |
| Tom   |   3900 |   1001 |      .2805755395683453237410071942446043165468 |
| Mery  |   1500 |   1001 |       .107913669064748201438848920863309352518 |
+-------+--------+--------+------------------------------------------------+