CODEWITHSUNDEEP
sqljoinsum
Runeaway3
Runeaway3

Reputation: 1449

What is the best way to sum information from two separate tables in SQL with join?

I have two tables with the following structure:

CREATE TABLE COST1 (
    ID,
    COUNTER,
    COST
)

CREATE TABLE COST2 (
    ID,
    COUNTER,
    COST
)

ID can be used for a JOIN; and while COUNTER and COST are have the same name in both tables they are not related to each other the way ID is. I would like to create a result set COST3 that has the form:

ID, sum(COST1.cost) + sum(COST2.cost).

Here is what I have come up with but I don't know if summing over the original tables with a GROUP BY that results from the JOIN would work as I intend?

SELECT
    ID, 
    ( sum(c1.COST) + sum(c2.COST) ) as COST_TOTAL
FROM
    COST1 c1
    JOIN COST2 c2 ON c1.ID = c2.ID
GROUP BY
    ID;

With some data, here is what the result should look like:

COST1

ID Counter Cost
A 1 50
A 2 30
B 1 25
B 2 30

COST2:

ID Counter Cost
A 1 20
A 2 40
B 1 50
B 2 10
B 3 20

COST3:

ID Cost
A, 140
B, 135

Upvotes: 2

Views: 87

Answers (5)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4253

union all the two table then group by id the subquery. use a case when to partition between the two tables a specific column value.

 create table #COST1(ID varchar(5), Counter int,Cost int);
  insert into #COST1 values('A', 1, 50  );
  insert into #COST1 values('A', 2, 30  );
  insert into #COST1 values('B', 1, 25  );
  insert into #COST1 values('B', 2, 30  );
  insert into #COST1 values('C', 2, 30  );


  create table #COST2(ID varchar(5), Counter int,Cost int);   
  insert into #COST2 values('A', 1, 20  );
  insert into #COST2 values('A', 2, 40  );
  insert into #COST2 values('B', 1, 50  );
  insert into #COST2 values('B', 2, 10  );
  insert into #COST2 values('B', 3, 20  );
  insert into #COST2 values('D', 3, 20  );

   SELECT ID, 
   Sum(case when Type='Cost1' then Cost else 0 end+case when Type='Cost2' then Cost       else 0 end) Cost, 
   Sum(Counter) Counter
   FROM
   (
   SELECT *,'Cost1' Type FROM #COST1
   UNION ALL
   SELECT *,'Cost2' Type FROM #COST2
   )x
   group by ID

   drop table #COST1
   drop table #COST2

output

ID  Cost    Counter
A   140     6
B   135     9
C   30      2
D   20      3

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

 create table COST1(ID varchar(5), Counter int,Cost int);
 insert into COST1 values('A', 1, 50  );
 insert into COST1 values('A', 2, 30  );
 insert into COST1 values('B', 1, 25  );
 insert into COST1 values('B', 2, 30  );
 insert into COST1 values('C', 2, 30  );
  
 
 create table COST2(ID varchar(5), Counter int,Cost int);   
 insert into COST2 values('A', 1, 20  );
 insert into COST2 values('A', 2, 40  );
 insert into COST2 values('B', 1, 50  );
 insert into COST2 values('B', 2, 10  );
 insert into COST2 values('B', 3, 20  );
 insert into COST2 values('D', 3, 20  );

Query#1 (To get sum for all the IDs which are available in both tables)

 Select C1.ID, (C1.cost+C2.cost) as Cost
 from
     (select ID, sum(COST) cost from COST1
 group by ID) C1
 
 Inner join
     (select ID, sum(COST) cost from COST2
 group by ID) C2
 
 on C1.ID=C2.ID
 GO

Output:

ID Cost
A 140
B 135

Query#2 (To get sum for all the IDs which are available in any of the tables)

 select ID, sum(cost) Cost
 from (
     (select id, cost
        from cost1
     ) union all
     (select id, cost
      from cost2
     )
     ) Cost3
 group by id;
 GO

Output:

ID Cost
A 140
B 135
C 30
D 20

db<fiddle here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would be inclined to use union all in a subquery and then aggregate:

select id, sum(cost)
from ((select id, cost
       from cost1
      ) union all
      (select id, cost
       from cost2
      )
     ) ic
group by id;

Upvotes: 0

Bohemian
Bohemian

Reputation: 425198

Use a sum over a union of a sum:

select id, sum(s) COST_TOTAL
from (
  select id, sum(cost) s
  from cost1
  group by id
  union all
  select id, sum(cost)
  from cost2
  group by id
) u
group by id

Note that you need the “all” version of union to preserve duplicate rows.

Upvotes: 0

Dai
Dai

Reputation: 155418

Consider performing the aggregation of non-unique ID rows in inner-queries:

SELECT
    COALESCE( c1.group_id, c2.group_id ) AS id,
    ( COALESCE( c1.group_cost, 0 ) + COALESCE( c2.group_cost, 0 ) ) AS total_cost
FROM
    (
        SELECT
            cost1.id AS group_id,
            SUM( cost1.cost ) AS group_cost
        FROM
            cost1
        GROUP BY
            cost1.id
    ) AS c1
    FULL OUTER JOIN
    (
        SELECT
            cost2.id AS group_id,
            SUM( cost2.cost ) AS group_cost
        FROM
            cost2
        GROUP BY
            cost2.id
    ) AS c2
        ON c1.group_id = c2.group_id
)
ORDER BY
    id

Upvotes: 0

Related Questions