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.