"What share of revenue does each payment method bring?" needs each value divided by the total, which usually means computing the total in a subquery. RATIO_TO_REPORT does it in one analytic function: it returns each value's fraction of the total of its partition.
Code for This Guide
The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
ratio_to_report(expr) over ([partition by expr [, ...]])
The result is expr / SUM(expr) over the partition. OVER () makes the whole result one partition. RATIO_TO_REPORT takes no ORDER BY or frame. Multiply by 100 for a percentage.
Share of Each Payment Method
Here the argument is itself an aggregate, SUM(amount), so the ratio is computed over the grouped rows.
Example:
select payment_method, sum(amount) as total,
round(100 * ratio_to_report(sum(amount)) over (), 1) as pct
from payments
where amount > 0
group by payment_method
order by total desc;Output:
PAYMENT_METHOD TOTAL PCT _________________ ____________ _______ CARD 605271.55 53.5 WALLET 203615.74 18 BANK TRANSFER 169415.41 15 MILES 152475.95 13.5
Cards bring 53.5 percent of the payments, and the four shares add up to 100.
Shares within Groups
With PARTITION BY, each share is of its own group's total: here each cabin's share of the fares within each booking status.
Example:
-- each cabin's share of the fares within each booking status
select b.status, t.cabin, sum(t.fare) as fares,
round(100 * ratio_to_report(sum(t.fare)) over (partition by b.status), 1) as pct_of_status
from bookings b join tickets t on t.booking_id = b.booking_id
group by b.status, t.cabin
order by b.status, t.cabin;Output:
STATUS CABIN FARES PCT_OF_STATUS ____________ ___________ ____________ ________________ CANCELLED BUSINESS 50245.14 68.4 CANCELLED ECONOMY 23218.59 31.6 COMPLETED BUSINESS 410381.36 53.8 COMPLETED ECONOMY 351704.41 46.2 CONFIRMED BUSINESS 157719.29 53.4 CONFIRMED ECONOMY 137509.86 46.6 6 rows selected.
Business class brings about 54 percent of the fares of completed and confirmed bookings, but 68 percent of the cancelled fares.
Things to Know
- NULL values give NULL ratios and are left out of the total.
- If the total of a partition is 0, the ratio is NULL rather than a division error.
- The same result can be written as x / SUM(x) OVER (PARTITION BY ...), which also allows other denominators.
Related Guides
Conclusion
RATIO_TO_REPORT returns each value's fraction of its partition's total, so shares and percentages need no subquery. Use OVER () for shares of the whole result and PARTITION BY for shares within groups.
