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

    calculation query

    991353
      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
          Rahul_India
          Use analytical functions
          • 2. Re: calculation query
            BluShadow
            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
              Stew Ashton
              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.)