14

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.

0
23

You're looking for the analytical function ratio_to_report

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

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);
0
5

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

WITH TOTAL_COMMISSION AS 
(SELECT SUM(COMMISSION) AS TOTAL FROM AGENTS)
SELECT A.AGENT_NAME, A.COMMISSION, ((A.COMMISSION/T.TOTAL)*100) AS "% COMMISSION"
FROM AGENTS A, TOTAL_COMMISSION T;
0
0
  select 
  Agent, Commission,
  (
      ROUND(
       (Commission *100) / 
          (
            (SELECT SUM(Commission)
             FROM commissions AS A)
          )
       ) 
  ) AS Porcentaje
  from  
  commissions

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.