Calculating percentage of a row over total sum

aggregateoracle-10g-r2

Apologies for the bad title, I wasn't sure what would be a good title for this.

This is currently (simplified view of the) data I'm working with

Agent    |  Commission     
---------|------------
Smith    |    100
Neo      |    200
Morpheus |    300

I need to calculate the percentage of the total commission, each agent is responsible for.

So, for Agent Smith, the Percentage would be calculated as (Agent Smith's commission / Sum(commission)*100

So, my expected data would be

Agent    |  Commission   |  % Commission    
---------|---------------|---------------
Smith    |    100        |     17
Neo      |    200        |     33
Morpheus |    300        |     50

I have a function returning the commission for each agent. I have another function returning the percentage as (Commission/Sum(Commission))*100. The problem is that Sum(commission) gets calculated for each and every row, and given that this query would be run on a Data Warehouse, the data set would be rather large ( currently, it's just under 2000 records) and quite honestly, a bad approach (IMO).

Is there a way of having the Sum(Commission) not calculate for every row being fetched ?

I was thinking something on the lines of a 2 part query, the first part would fetch the sum(commission) into a package variable/type and the second part would refer to this pre-calculated value, but I'm not sure how I can accomplish this.

I am limited to using SQL, and I'm running on Oracle 10g R2.

Best Answer

You're looking for the analytical function ratio_to_report

select 
  agent,
  round(ratio_to_report(commission) over ()*100) "% Comm."
from  
  commissions;