CODEWITHSUNDEEP
mysqljoinouter-join
JOB
JOB

Reputation: 85

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

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 expected results From the two table1& table2

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

Upvotes: 0

Views: 141

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272236

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

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

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)

Upvotes: 0

GMB
GMB

Reputation: 222582

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

Upvotes: 0

Related Questions