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