Wrong calculation in Sum()

Question

hi i have 2 table and i want plus it.

Table 1 --> StudentId = 1 ,Score = 10 , 20 ,30  , StudentId = 2 ,Score = 5, 5
Table 2 --> StudentId = 1 ,Score = 5, 10 ,15    , StudentId = 2 ,Score = 15, 25
Total = StudentId = 1 ---> 90  , StudentId = 2 ---> 45

i use this query:

Select Sum(tbl_EvaPoint.Score + tbl_ActPoint.Score ), 
       tbl_ActPoint.StudentId 
From tbl_EvaPoint 
JOIN tbl_ActPoint 
  ON tbl_EvaPoint.StudentId = tbl_ActPoint.StudentId 
GROUP BY tbl_ActPoint.StudentId`

everythings is ok but i get wrong sum instead of 90 and 45 i get 180 and 90 or somthings else.

Answer 1

Use UNION instead of JOIN

SELECT student_id, SUM(score)
FROM (SELECT student_id, score FROM Table1
      UNION ALL
      SELECT student_id, score FROM Table2
     ) as T
GROUP BY  student_id