3 Replies Latest reply: Feb 18, 2013 3:44 AM by Stew Ashton

# calculation query

Hi,
How to write the sql query for following calculation.

select customer,location,region,amount from PAC_Customer /*Custom table*/

Total%= sum(amount)/sum(sum(amount))*100

output result like:customer,location,region,amount,Total%

Please let me know the query.

Thank you.
• ###### 1. Re: calculation query
Use analytical functions
• ###### 2. Re: calculation query
988350 wrote:
Hi,
How to write the sql query for following calculation.

select customer,location,region,amount from PAC_Customer /*Custom table*/

Total%= sum(amount)/sum(sum(amount))*100

output result like:customer,location,region,amount,Total%

Please let me know the query.

Thank you.
What do you hope to get from "sum(sum(amount))"? Once the amounts are summed, you have a single value, so what good is there in summing it again?
Are you sure you don't want

>
total%=(amount/sum(amount))*100
>

and for good measure it's usual to round these things..

>
total%=round((amount/sum(amount))*100,2)
>

e.g.
``````SQL> ed
Wrote file afiedt.buf

1  select deptno, empno, ename, sal
2        ,round((sal/sum(sal) over (partition by deptno))*100,2) as "dept_total%"
3        ,round((sal/sum(sal) over ())*100,2) as "overall_total%"
4  from   emp
5* order by deptno, empno
SQL> /

DEPTNO      EMPNO ENAME             SAL dept_total% overall_total%
---------- ---------- ---------- ---------- ----------- --------------
10       7782 CLARK            2450          28           8.44
10       7839 KING             5000       57.14          17.23
10       7934 MILLER           1300       14.86           4.48
20       7369 SMITH             800        7.36           2.76
20       7566 JONES            2975       27.36          10.25
20       7788 SCOTT            3000       27.59          10.34
20       7876 ADAMS            1100       10.11           3.79
20       7902 FORD             3000       27.59          10.34
30       7499 ALLEN            1600       17.02           5.51
30       7521 WARD             1250        13.3           4.31
30       7654 MARTIN           1250        13.3           4.31
30       7698 BLAKE            2850       30.32           9.82
30       7844 TURNER           1500       15.96           5.17
30       7900 JAMES             950       10.11           3.27

14 rows selected.``````
• ###### 3. Re: calculation query
If I understand the requirement correctly, you want the subtotal amount for each customer, location and region divided by the total amount for everyone. In that case:
``````SELECT CUSTOMER,LOCATION,REGION,SUM(AMOUNT) SUM_AMOUNT,
SUM(SUM(AMOUNT)) OVER() TOT_AMOUNT,
round(SUM(AMOUNT) * 100 / SUM(SUM(AMOUNT)) OVER(), 2) Tot_pct
FROM PAC_CUSTOMER
GROUP BY CUSTOMER,LOCATION,REGION
order by CUSTOMER,LOCATION,REGION;``````
SUM(AMOUNT) is an aggregation based on the GROUP BY.

SUM(...) OVER() is an analytical function applied to each row after the aggregation is done.

So SUM(SUM(AMOUNT)) OVER() is an analytical sum of the aggregated sums. It gives you the grand total on every line, which allows you to calculate the percentage you want.

(Based on ideas and explanations from Rahul, BluShadow, Solomon and Frank.)