How to Join two tables and sum their columns across in Mysql based on distinct column

Question

I am joining two tables by the regNo column. I want to add the points of Table1.points and Table2.points where the regNo matches and just incase it doesn't match I also want it be included with its points in the list as shown in the image bellow

I have read through the existing problems but not finding the solution to this e.g How can I sum columns across multiple tables in MySQL?

(
    SELECT `Ex`.regNo,(`In`.`points`+`Ex`.`points`) AS 'Points' 
    FROM Table1`In` 
    LEFT JOIN Table2`Ex` ON `In`.`regNo` = `In`.`regNo`
)
UNION
(
    SELECT`Ex`.regNo,(`In`.`points`+`Ex`.`points`) AS 'Points' 
    FROM Table1`In` 
    RIGHT JOIN Table2`Ex` ON `In`.`regNo` = `In`.`regNo`
);

I want it to give the list arranged as per unique (DISTINCT) regNo

score 1 · Accepted Answer · answered Jan 04 '19 at 22:26

1

You need UNION followed by GRoUP BY:

SELECT regNo, SUM(points) AS total
FROM (
    SELECT regNo, points
    FROM Table1

    UNION ALL

    SELECT regNo, points
    FROM Table2
) AS u
GROUP BY regNo

answered Jan 04 '19 at 22:26

Salman A

262,204
82
430
521

Thanks alot this was so simple and worked well with my case. – JOB Jan 04 '19 at 23:01

score 0 · Answer 2 · answered Jan 04 '19 at 22:12

You are looking for a FULL JOIN between both tables.

SELECT 
    COALESCE(t1.id, t2.id) id
    COALESCE(t1.regNo, t2.regNo) regNo
    COALESCE(t1.points, 0) +  COALESCE(t2.points 0) points
FROM
    table1 t1
    FULL JOIN table2 t2 on t1.regNo = t2.regNo

NB : you did not specify what you expect to be done to generate the new id so by default the above query will display the table1.id if available, else table2.id.

If you would better generate a new, auto-incremented field, then :

SET @i=0;
SELECT 
    @i:=@i+1 id
    COALESCE(t1.regNo, t2.regNo) regNo
    COALESCE(t1.points, 0) +  COALESCE(t2.points 0) points
FROM
    table1 t1
    FULL JOIN table2 t2 on t1.regNo = t2.regNo

I am not Much interested in Id generation. I just want that unique regNo and the sum from both tables as shown above — JOB, Jan 04 '19 at 22:18

Derviş Kayımbaşıoğlu · Answer 3 · 2019-01-04T22:19:22.107

0

Please check this. You need to use full outer join and null replacement before aggregation

select 
   COALESCE(table1.regno, table2.regno) regno,
   sum(COALESCE(table1.points,0)) + sum(COALESCE(table2.points,0)) points
from Table1 
full outer join Table2 
  on table1.regno = table2.regno
group by 
  COALESCE(table1.regno, table2.regno)

edited Jan 04 '19 at 22:19

answered Jan 04 '19 at 22:12

Derviş Kayımbaşıoğlu

28,492
4
50
72

How to Join two tables and sum their columns across in Mysql based on distinct column

3 Answers3