Need an efficient query for sum and sum of sums

user_2806262 0
Viewed 857

I have a sql query to select the sum of amounts as per below scenario.

There are managers who look after sales of many locations. Sale amounts of all managers of all locations are stored in the same table. I want to select the Manager name, sum of amounts of locations and his total amount from overall table.

Following is the SQL which I have. It is working. But looking for more efficient way of implementation considering 100k records in the table.

SELECT 
D1.Manager_name,
D1.location_name,
sum(D1.sale_amount) sale_amount,
(select sum(D2.sale_amount) from details D2 where D1.Manager_name = D2.Manager_name) total_amount
FROM details D1
GROUP BY 
D1.Manager_name,
D1.location_name;

Table data and expected output data

sum oracle sql
2 Answers

You may try using SUM as a window function, to replace the correlated subquery:

SELECT 
    D1.Manager_name,
    D1.location_name,
    SUM(D1.sale_amount) sale_amount,
    SUM(SUM(D1.sale_amount)) OVER (PARTITION BY D1.Manager_name) total_amount
FROM details D1
GROUP BY 
    D1.Manager_name,
    D1.location_name;

Here is an explanation of what is happening. Window functions always are evaluated last. The only thing which executes after a window function is the ORDER BY clause. In the above case, after GROUP BY evaluates, the only columns available in the intermediate result are Manager_name, location_name, and SUM(sale_amount). When we use SUM as a window function, with a partition by manager, we therefore can find the total sum for each manager, across all aggregated locations.

user_18632290

If you accept another way of displaying the data, there is an extension of the GROUP BY clause that will give you both the subtotals and the totals you want in one pass. Here I generate 99999 rows and get the output in 0.03 seconds.

with m(manager_name) as (
  select 'Mgr '||level from dual connect by level <= 3
)
, l(location_name) as (
  select 'Location '||level from dual connect by level <= 3
)
, s(sale_amount) as (
  select level from dual connect by level <= 100000/9
)
select  
Manager_name,
location_name,
sum(sale_amount) sale_amount
FROM m, l, s
GROUP BY Manager_name,
rollup(location_name);

MANAGER_NAME LOCATION_NAME SALE_AMOUNT
------------ ------------- -----------
Mgr 1        Location 1       61732716
Mgr 1        Location 2       61732716
Mgr 1        Location 3       61732716
Mgr 1                        185198148
Mgr 2        Location 1       61732716
Mgr 2        Location 2       61732716
Mgr 2        Location 3       61732716
Mgr 2                        185198148
Mgr 3        Location 1       61732716
Mgr 3        Location 2       61732716
Mgr 3        Location 3       61732716
Mgr 3                        185198148
user_63307970
Related