# 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;
``````
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
``````
0
Author 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
``````
0
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
Author 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
Commented:
Can you add that to your example?  Then we can mock up a test case and provide tested SQL.
0
Author 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
``````
0
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
/
``````
0
Author 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
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
Author 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
``````
0
Commented:
>>My expected results is:  Y - X

Please provide the exact number given the 6 rows.  What value should my query return?
0
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
Author 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.
``````
0
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
/
``````
1
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

Experts Exchange Solution brought to you by