Sum of Sum 1

[Sambo8]

Hi All,

What I want to do is make a sum out of the results of 2 sums:-

my select looks like this:-

Select sum(fld1 * fld2) as res1, sum(fld3 * fld4 / 100) as res2

what I now want to do is another sum which is res1 - res2. What is the best way of doing this?

Many thanks

Sam

 

[SantaMufasa]

what I now want to do is another sum which is res1 - res2

"Summing sums" implies some sort of "grouping" of the initial sums based upon some criteria/criterion, but you have not indicated what those criteria might be. Therefore, without grouping criteria, your high-level "summing of sums", for the time being is limited to a simple subtraction operation between your initial "SUMs":

Section 1 -- Sample data:

SQL> select * from sambo;

      FLD1       FLD2       FLD3       FLD4
---------- ---------- ---------- ----------
         1          2          3          4
         1          2          3          4
         1          2          3          4
         5          6          7          8
         5          6          7          8
         5          6          7          8

6 rows selected.

Section 2 -- Results from your original SELECT:

SQL> Select sum(fld1 * fld2) as res1, sum(fld3 * fld4 / 100) as res2 from sambo;

      RES1       RES2
---------- ----------
        96       2.04

Section 3 -- Results that subtracting the initial SUMs:

SQL> select sum(fld1 * fld2)-sum(fld3 * fld4 / 100) Result from sambo;

    RESULT
----------
     93.96

I/we can assert a "SUMming of Subtracted SUMs" if you can post by what criteria you wish to GROUP your individual SUMs.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[LKBrwnDBA]

Did you try:

Select sum(fld1 * fld2) - sum(fld3 * fld4 / 100) as "res1-res2" from myTable;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Sambo8]

Hi both,

Apologies here is my actual code, there is already one grouping being done, I need a third sum column which is res1 - res2:

select a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0, e.itmref_0, f.itmdes1_0, e.qty_0, h.baspri_0,
i.discrgval1_0, g.sohnum_0, sum(e.qty_0 * h.baspri_0) as res1, sum(e.qty_0 * h.baspri_0 / 100 * i.discrgval1_0) as res2
from bpplive.sinvoice a, bpplive.bpcustomer b, bpplive.bpartner c, bpplive.bpaddress d, bpplive.sinvoiced e, bpplive.itmmaster f, bpplive.sorderq g, bpplive.itmsales h, bpplive.sorderp i
where a.bprpay_0 = b.bpcnum_0
and b.bpcnum_0 = c.bprnum_0
and c.bprnum_0 = d.bpanum_0
and c.bpaadd_0 = d.bpaadd_0
and a.num_0 = e.num_0
and e.itmref_0 = f.itmref_0
and e.sohnum_0 = g.sohnum_0
and e.soplin_0 = g.soplin_0
and g.drn_0 = 1
and e.itmref_0 = h.itmref_0
and g.sohnum_0 = i.sohnum_0
and g.soplin_0 = i.soplin_0
group by a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0, e.itmref_0, f.itmdes1_0, e.qty_0, h.baspri_0, i.discrgval1_0, g.sohnum_0

any help greatly appreciated.

Many thanks

Sam

 

[SantaMufasa]

Off the top of my (balding) head, how about using an in-line VIEW of your original SELECT:

select bprpay_0, bpcnam_0, bpaaddlig_0, itmref_0, itmdes1_0,
       qty_0, baspri_0, discrgval1_0, sohnum_0, sum(res1-res2) "Res1-Res2"
  FROM (select a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0, e.itmref_0,
               f.itmdes1_0, e.qty_0, h.baspri_0, i.discrgval1_0, g.sohnum_0,
               sum(e.qty_0 * h.baspri_0) as res1,
               sum(e.qty_0 * h.baspri_0 / 100 * i.discrgval1_0) as res2
          from bpplive.sinvoice a, bpplive.bpcustomer b,
               bpplive.bpartner c, bpplive.bpaddress d,
               bpplive.sinvoiced e, bpplive.itmmaster f,
               bpplive.sorderq g, bpplive.itmsales h, bpplive.sorderp i
         where a.bprpay_0 = b.bpcnum_0 
           and b.bpcnum_0 = c.bprnum_0
           and c.bprnum_0 = d.bpanum_0
           and c.bpaadd_0 = d.bpaadd_0
           and a.num_0 = e.num_0
           and e.itmref_0 = f.itmref_0
           and e.sohnum_0 = g.sohnum_0
           and e.soplin_0 = g.soplin_0
           and g.drn_0 = 1
           and e.itmref_0 = h.itmref_0
           and g.sohnum_0 = i.sohnum_0
           and g.soplin_0 = i.soplin_0
         group by a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0,
                  e.itmref_0, f.itmdes1_0, e.qty_0, h.baspri_0,
                  i.discrgval1_0, g.sohnum_0)
 group by bprpay_0, bpcnam_0, bpaaddlig_0, itmref_0, itmdes1_0,
          qty_0, baspri_0, discrgval1_0, sohnum_0;

Let us know how this turns out for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Sambo8]

Dave,

Thats perfect!!!! Thank you very much;-)

Sam

 

[Beilstwh]

Why not simply use the sum values twice, it will NOT take any more time. See below.

select a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0, e.itmref_0, f.itmdes1_0, e.qty_0, h.baspri_0,
i.discrgval1_0, g.sohnum_0, sum(e.qty_0 * h.baspri_0) as res1, sum(e.qty_0 * h.baspri_0 / 100 * i.discrgval1_0) as res2,
sum(e.qty_0 * h.baspri_0) - sum(e.qty_0 * h.baspri_0 / 100 * i.discrgval1_0) AS RES3
from bpplive.sinvoice a, bpplive.bpcustomer b, bpplive.bpartner c, bpplive.bpaddress d, bpplive.sinvoiced e, bpplive.itmmaster f, bpplive.sorderq g, bpplive.itmsales h, bpplive.sorderp i
where a.bprpay_0 = b.bpcnum_0
and b.bpcnum_0 = c.bprnum_0
and c.bprnum_0 = d.bpanum_0
and c.bpaadd_0 = d.bpaadd_0
and a.num_0 = e.num_0
and e.itmref_0 = f.itmref_0
and e.sohnum_0 = g.sohnum_0
and e.soplin_0 = g.soplin_0
and g.drn_0 = 1
and e.itmref_0 = h.itmref_0
and g.sohnum_0 = i.sohnum_0
and g.soplin_0 = i.soplin_0
group by a.bprpay_0, b.bpcnam_0, d.bpaaddlig_0, e.itmref_0, f.itmdes1_0, e.qty_0, h.baspri_0, i.discrgval1_0, g.sohnum_0

Bill
Oracle DBA/Developer
New York State, USA