Many reports include a percentage of the report total on each line, in addition to the detail data being reported. For example, what percentage does each customer contribute to all of the orders placed in a month, or what percentage does each sales rep contribute to the total sales?
The traditional way to do this in Oracle is to use the SUM function in a subquery to total the report, and then join that result to the detail table and divide to calculate the percentage. You can also use a subquery as one of the SELECT statement expressions.
The RATIO_TO_REPORT analytic function makes this type of query much easier to code. Available since Oracle 8i, the format is:
RATIO_TO_REPORT (expr) OVER (query_partition_clause)
Listing A shows a simple example. For sales rep #163, how much did each customer contribute to the sales rep’s overall sales total? In this case, the query partition clause is empty, so results are computed over the entire set of rows returned. Listing B adds a query partition clause to further subdivide the report by customer.
The clause PARTITION BY customer_id causes the totals to be reset for each customer and, therefore, shows which orders from each customer were most significant. You can see that for customer 102, the two orders are relatively balanced, but for customer 103, one order accounted for most of that customer’s total.
Oracle’s analytic functions save you time in coding and can result in more efficient query plans during execution.
Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob’s site.
Get Oracle tips in your inbox
TechRepublic’s free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!