Question 240973 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sum from Two Tables by Joining

Let say I have two table with 2 columns :
Table A :

Job Cost
1 100
1 200
2 100
2 150
2 50

And Table B :

Job Sell
1 100
1 100
1 200
2 100
2 100

And I want result query by joining these two tables, as below :

Job Cost Sell
1 300 400
2 300 200

I try the query as below and the result not as expected :

SELECT A.Job, 
       SUM(A.Cost) AS NCOST,
       SUM(B.Sell) AS NSELL   
FROM TableA A 
JOIN TableB B ON A.Job= B.Job
GROUP BY A.Job;

Can anyone help me to correct my query ?

Tags:

3

Answers


  1. 0

    You need to calculate totals separately, then join the two datasets :

    SELECT c.Job, c.COST, s.Sell
FROM (
  SELECT Job, SUM(Cost) AS COST
  FROM TableA
  GROUP BY Job
) AS c
INNER JOIN (
  SELECT Job, SUM(Sell) AS Sell
  FROM TableB
  GROUP BY Job
) AS s on s.Job = c.Job

    Demo here

    Login or Signup to reply.
  2. 0

    You need to aggregate each table first, so that each Job value appears only once, and then join the two aggregated datasets.

    At the moment, as Job appears multiple times in the first table if you just join the two tables you’ll get duplicated rows

    Login or Signup to reply.
  3. 0

    Instead of a join use UNION ALL and then aggregate:

    SELECT Job,
       SUM(Cost) AS Cost,
       SUM(Sell) AS Sell
FROM (
  SELECT Job, Cost, 0 AS Sell FROM TableA
  UNION ALL
  SELECT Job, 0, Sell FROM TableB
) AS t
GROUP BY Job;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search