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.

  • Not obviously a DBA question (perhaps if it were tablespaces rather than salesmen?) - should probably be on Stack Overflow. – Gaius Jan 17 '11 at 19:48

You're looking for the analytical function ratio_to_report

  round(ratio_to_report(commission) over ()*100) "% Comm."
| improve this answer | |
  • Awesome, didn't know about this, thanks! – Sathyajith Bhat Jan 18 '11 at 5:15

To return all agents with their commissions and commission percentages use an analytic function with no analytic clause so that the partition is over the whole table:

SELECT Agent, commission, 100* commission / (SUM(commission) OVER ()) "% Commission" 
FROM commissions;

As I learned from René Nyffenegger (+1) the ratio_to_report function tightens this syntax.

Using a package to store the Commission SUM would involve PL/SQL, which you specifically excluded by indicating that you want a SQL solution, but since you are already using functions I assume your intention was not to exclude PL/SQL. If this is the case, then the package solution may help, but it depends on how your application works.

When your session is first created and calls the function in the package to get the commission there is an implicit call to the packages constructor which could get the sum and store it. Then you could reference the stored sum in your get commission function and it would only have to do the sum once. Of course as soon as you call the function from a different session the sum would be calculated again. Also, calling the function for every agent would be considerably less efficient than calling one SQL statement for all agents if your application can be designed in that manner.

You may want to consider turning your function into a procedure that returns a cursor for the query above or perhaps have a function that returns the results of the query as a pipelined results set.

Sample data:

create table commissions (Agent Varchar2(100), Commission Number(3));
insert into commissions values ('Smith',100);
insert into commissions values ('Neo',200);
insert into commissions values ('Morpheus',300);
| improve this answer | |

You could try the following query, sum(commission) will only be calculated once:

| improve this answer | |
  • That works and returns the correct data, but is less efficient than an analytic function that does one full table scan rather than two (assuming no indexes). – Leigh Riffel Jan 17 '11 at 16:32
  • 1
    @Leigh ~ How can it do it in one pass since the manual way requires two passes? I can't see how computers could make %ofTotal a magical one-pass operation... – jcolebrand Jan 17 '11 at 19:53
  • @jcolebrand The data is only read from the database blocks once. It is probably doing multiple passes of its in memory results, but this is generally faster than reading the database blocks twice. There are tradeoffs in memory and CPU between these options, so the choice may not always be clear cut, but in this case I think it is. – Leigh Riffel Jan 17 '11 at 20:19
  • 1
    @Leigh ~~ Yeah, further consideration would lead me to believe that's all it could be doing, just black box jittered optimizations. Anyways, a nifty solution in your answer. Thanks :D – jcolebrand Jan 17 '11 at 20:26
  Agent, Commission,
       (Commission *100) / 
            (SELECT SUM(Commission)
             FROM commissions AS A)
  ) AS Porcentaje
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.