How to calculate a Difference in sum totals resulting from 2 sets of queries

From the below code snippet, how would I calculate the total difference resulting from 2 sets of query ( I need to find value of  total_2 - total_1  in this code snippet below)

Procedure procA
...
..
  INSERT INTO TABLE_A
  (
   col1,
   col2,
   col3,
   col4,
   ...
   ...
   )
  SELECT 
    col1,
    col2,
    col3,
    sum(interval_values) as total_1 -- datatype float
  FROM one_list_of_tables;

  COMMIT;

  INSERT INTO TABLE_A
  (
   col1,
   col2,
   col3,
   col4,
   col5
   )
  SELECT 
    col1,
    col2,
    col3,
    sum(interval_values) as total_2  -- datatype float
    total_2 - total_1 as total_3        
  FROM different_list_of_tables;

  COMMIT;
..
..
..
END procA;

Open in new window

steve2312Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
If you are 100% sure there are 100 rows in each set, then you should just be able to use AVG instead of the SUM/100 but it probably won't save you much processing time.
0
 
slightwv (䄆 Netminder) Commented:
Does each one of those inserts only insert a single row or multiple rows?

If TABLE_A  only ends up with two rows, it is pretty easy:
select max(diff) from ( select col4-lead(col4) over(order by col1) diff from TABLE_A);

If TABLE_A has more than two rows from the two inserts, we need to know what row/value goes with what.  Please provide sample data for TABLE_A after the inserts and the expected results.
1
 
PortletPaulfreelancerCommented:
In my experience, when you try to "simplify" to the extent seen in that snippet important details get lost in translation. It would be preferred (in my opinion) if you used real table and column names.  Anyway, it "looks like" col1, col2 and col3 would be used to join the 2 result sets.  Sample data would help a great deal.

e.g.
select
       s1.col1
     , s1.col2
     , s1.col3
     , s1.total_1
     , s2.total_2 
     , s2.total_2 - s1.total_1 as total_3        
FROM (
      SELECT 
          col1,
          col2,
          col3,
          sum(interval_values) as total_1 -- datatype float
        FROM one_list_of_tables
        GROUP BY
          col1,
          col2,
          col3
      ) S1
INNER JOIN (
        SELECT 
          col1,
          col2,
          col3,
          sum(interval_values) as total_2  -- datatype float      
        FROM different_list_of_tables
        GROUP BY
          col1,
          col2,
          col3
      ) S2 on s1.col1=s2.col1
          and s2.col1=s2.col2
          and s3.col1=s2.col3

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
steve2312Author Commented:
slightwv - Each INSERT would have multiple rows inserted.

PortletPaul - Here are the list of columns with some sample data/expected results

Procedure procA
...
..
  INSERT INTO energy_extract
  (
   energid,
   transcode,
   transname,
   transdate,
   dailyintvtotal
   )
  SELECT 
    energyid,
    transcode,
    transname,
    sum(interval_val/100) as dailyenergyintv
  FROM vw_energy_intv;

  COMMIT;

  INSERT INTO TABLE_A
  (
   energid,
   transcode,
   transname,
   transdate,
   col5
   )
  SELECT 
    energyid,
    transcode,
    transname,
    sum(interval_val/100) as dailyemmintv
    dailyemmintv - dailyenergyintv as dailyintvtotal       
  FROM vw_emmission_intv

  COMMIT;
..
..
..
END procA;


INSERT INTO energy_extract
values
(10032789411697000, 359, 'STAR ENERGY SERVICES', 11/1/2016, 10334)
(10032789412313900, 424, 'EDD ENERGY SERVICES', 11/15/2016, 10165)
(10032789415965000, 385, 'LDM ENERGY CORP', 11/13/2016, 10772)
..
..
..
.. a total of 100 rows..
-- 

SECOND SET

INSERT INTO energy_extract
VALUES
(10032789421946500, 165, 'ABA ENERGY MARKETING', 11/2/2016, 0)
(10032789445491100, 122, 'RELIANCE CORP', 11/9/2016, 12279)
(10032789461125500, 284, 'TRAVERSE ENERGY CORP', 11/17/2016, 11880)
..
..
and a total of 100 rows..

Expected results FOR COLUMN DAILYINTVTOTAL : dailyintvtotal = Y - X

Set 1: (10334 + 10165 + 10772....../100) = X

SET 2: (0 + 12779 + 11880..../100) = Y

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
How do you know which rows were inserted as part of set1 versus set2?  You will need some column to distinguish between the batches.
0
 
steve2312Author Commented:
Sorry I missed adding the column in the list of sample values.  It'd be  a database sequence populated thru a trigger for every row inserted into the table
0
 
slightwv (䄆 Netminder) Commented:
Can you add that to your example?  Then we can mock up a test case and provide tested SQL.
0
 
steve2312Author Commented:
Added the database sequence to the code snippet...

Procedure procA
...
..
  INSERT INTO energy_extract
  (
   energid,
   transcode,
   transname,
   transdate,
   dailyintvtotal
   )
  SELECT 
    energyid,
    transcode,
    transname,
    sum(interval_val/100) as dailyenergyintv
  FROM vw_energy_intv;

  COMMIT;

  INSERT INTO TABLE_A
  (
  uid_energid -- database sequence 
  energid,
   transcode,
   transname,
   transdate,
   col5
   )
  SELECT 
   -- NEXTVAL database sequence to populate for every insert a unique id differentiating each row
    energyid,
    transcode,
    transname,
    sum(interval_val/100) as dailyemmintv
    dailyemmintv - dailyenergyintv as dailyintvtotal       
  FROM vw_emmission_intv

  COMMIT;
..
..
..
END procA;


INSERT INTO energy_extract
values
(10001, 10032789411697000, 359, 'STAR ENERGY SERVICES', 11/1/2016, 10334)
(10002, 10032789412313900, 424, 'EDD ENERGY SERVICES', 11/15/2016, 10165)
(10003, 10032789415965000, 385, 'LDM ENERGY CORP', 11/13/2016, 10772)
..
..
..
.. a total of 100 rows..
-- 

SECOND SET

INSERT INTO energy_extract
VALUES
(20001, 10032789421946500, 165, 'ABA ENERGY MARKETING', 11/2/2016, 0)
(20002, 10032789445491100, 122, 'RELIANCE CORP', 11/9/2016, 12279)
(20003, 10032789461125500, 284, 'TRAVERSE ENERGY CORP', 11/17/2016, 11880)
..
..
and a total of 100 rows..

Expected results FOR COLUMN DAILYINTVTOTAL : dailyintvtotal = Y - X

Set 1: (10334 + 10165 + 10772....../100) = X

SET 2: (0 + 12779 + 11880..../100) = Y

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Here is my setup and SQL:
create table energy_extract(energyid number, dailyintvtotal number);
INSERT INTO energy_extract values(10001,10334);
INSERT INTO energy_extract values(10002,10165);
INSERT INTO energy_extract values(10003,10772);

INSERT INTO energy_extract values(20001,0);
INSERT INTO energy_extract values(20002,12279);
INSERT INTO energy_extract values(20003,11880);
commit;

select sum(case when energyid like '1%' then dailyintvtotal end)-sum(case when energyid like '2%' then dailyintvtotal end)
from energy_extract
/

Open in new window

0
 
steve2312Author Commented:
Do we need a SUM on the value for the first set of intervals?

The first INSERT statement has the totals already summed up and there is a commit inside the procedure - followed by the second set of INSERT statements.
0
 
slightwv (䄆 Netminder) Commented:
Even though the first set totals are summed, I thought you wanted the sum of the entire set1 subtracted from the sum of the entire set2.

Given the 6 rows in my sample setup, what are your expected results?
0
 
steve2312Author Commented:
That's right, the sum of the entire second set - sum of entire first set.

My expected results is:  Y - X
For 100 set of values..
Set 1: (10334 + 10165 + 10772....../100) = X
SET 2: (0 + 12779 + 11880..../100) = Y

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>My expected results is:  Y - X

Please provide the exact number given the 6 rows.  What value should my query return?
0
 
slightwv (䄆 Netminder) Commented:
also:  is the 100 hard-coded or do I need to add up how many rows are in each batch?

SUM/NUMBER is AVERAGE.  Can I just take the AVERAGE instead?
0
 
steve2312Author Commented:
100 is hard-coded (there are 100 intervals).

The value for Set 1= X will be (10334 + 10165 + 10772....../100) = 312.71
SET 2 = Y will be  (0 + 12779 + 11880..../100) = 246.59

So the expected value would be -66.12.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
This returns -66.12
drop table energy_extract purge;
create table energy_extract(energyid number, dailyintvtotal number);
INSERT INTO energy_extract values(10001,10334);
INSERT INTO energy_extract values(10002,10165);
INSERT INTO energy_extract values(10003,10772);

INSERT INTO energy_extract values(20001,0);
INSERT INTO energy_extract values(20002,12779);
INSERT INTO energy_extract values(20003,11880);
commit;

select (sum(case when energyid like '2%' then dailyintvtotal end)/100)-(sum(case when energyid like '1%' then dailyintvtotal end)/100)
from energy_extract
/

Open in new window

1
 
steve2312Author Commented:
Thanks for your suggestion(s) slightvw.  The SUM option using the case statement works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.