Sum values of two different columns of two tables without calculating duplicates in sql-CodePudding

Table #1 - 1st year

StudentID	Marks
1	97
1	95
1	90
2	78
2	89

Table #2 - 2nd year

StudentID	Marks
1	87
1	75
2	79
2	95

When i tried to join these two tables using StudentID:

SELECT
    StudentID, 
    SUM(t1.marks), SUM(t2.marks) 
FROM
    t1 
JOIN
    t2 ON t1.StudentID = t2.StudentID 
WHERE
    studentID = 1 
GROUP BY
    studentID ;

It is calculating sum of all duplicates.

Expected result

1 | 282 | 162

CodePudding user response：

there is no need to join the tables, you can union both tables in a query and then use it as a sub-query and retrieve the calculation from it. try this:

select id, sum(grade)
from (select * from first_year union all select * from second_year)
group by id

CodePudding user response：

You can use with to sum the tables first then join one row each year

with t1 as (select 1 as StudentID,97 as Marks from dual union select 1,95 from dual union select 1,90 from dual union select 2,78 from dual union select 2,89 from dual),
     t2 as (select 1 as StudentID,87 as Marks from dual union select 1,75 from dual union select 2,79 from dual union select 2,95 from dual),
     t1_sum as (select StudentID, sum(Marks) as Sum from t1 group by StudentID),
     t2_sum as (select StudentID, sum(Marks) as Sum from t2 group by StudentID)
select t1_sum.StudentID, max(t1_sum.Sum), max(t2_sum.Sum)
from t1_sum join t2_sum on t1_sum.StudentID = t2_sum.StudentID
group by t1_sum.StudentID

STUDENTID   MAX(T1_SUM.SUM) MAX(T2_SUM.SUM)
1   282 162
2   167 174

Hope this helps