CODEWITHSUNDEEP
mysqldatabase
TeknoVenus
TeknoVenus

Reputation: 187

SQL Incorrect SUMS from multiple JOINS

I'm trying to sum multiple tables using Joins and Sums in MySQL and not having much success.

My Tables (Unnecessary Columns Removed)

Students

idStudent   studentname   studentyear
1           foobar        11
2           barfoo        11
3           thing         8

Athletics_Results 

idResult   idStudent   points
1          1           14
2          1           11
3          3           7
4          2           9

Team_Results

idTeamResults   year   points
1               11     9
2               8      8
3               7      14

So let me explain about the tables, because I admit they're poorly named and designed.

Students holds the basic info about each student, including their year and name. Each student has a unique ID.

Athletics_Results stores the results from athletics events. The idStudent column is a foreign key and relates to idStudent in the student column. So student foobar (idStudent 1) has scored 14 and 11 points in the example.

Team_Results stores results from events that more than one student took part in. It just stores the year group and points.

The Aim

I want to be able to produce a sum of points for each year - combined from both athletics_results and team_results. EG:

year   points
7      14     <-- No results in a_r, just 14 points in t_r
8      15     <-- 7 points in a_r (idResult 4) and 8 in t_r
11     43     <-- 14, 11, 9 points in a_r and 9 in t_r

What I've tried For testing purposes, I've not tried combining the a_r scores and t_r scores yet but left them as two columns so I can see what's going on.

The first query I tried:

SELECT students.studentyear as syear, SUM(athletics_results.points) as score, SUM(team_results.points) as team_score
FROM students
JOIN team_results ON students.studentyear = team_results.year
JOIN athletics_results ON students.idStudent = athletics_results.idStudent
GROUP BY syear;

This gave different rows for each year (as desired) but had incorrect SUMS. I learnt this was due to not grouping the joins.

I then created this code:

SELECT studentyear as sYear, teamPoints, AthleticsPoints
FROM students st

JOIN    (SELECT year, SUM(tm.points) as teamPoints
        FROM team_results tm
        GROUP BY year) tr ON st.studentyear = tr.year 

JOIN    (SELECT idStudent, SUM(atr.points) as AthleticsPoints
        FROM athletics_results atr
        ) ar ON st.idStudent = ar.idStudent

Which gave correct SUMS but only returned one year group row (e.g the scores for Year 11).

EDIT - SQLFiddle here: http://sqlfiddle.com/#!9/dbc16/. This is with my actual test data which is a bigger sample than the data I posted here.

Upvotes: 0

Views: 79

Answers (3)

Norbert
Norbert

Reputation: 6084

Can be done in multiple ways. My first thought is:

SELECT idStudent, year, SUM(points) AS totalPoints FROM (
SELECT a.idStudent, c.year, a.points+b.points AS points
FROM students a
INNER JOIN Athletics_Results b ON a.idStudent=b.idStudent
INNER JOIN Team_Results c ON a.studentyear=c.year) d
GROUP BY idStudent,year

Upvotes: 0

Alexander R.
Alexander R.

Reputation: 1756

Try this http://sqlfiddle.com/#!9/2bfb1/1/0

SELECT 
    year, SUM(points)
FROM
    ((SELECT 
        a.year, SUM(b.points) AS points
    FROM
        student a
    JOIN at_result b ON b.student_id = a.id
    GROUP BY a.year) UNION (SELECT 
        a.year, SUM(a.points) AS points
    FROM
        t_result a
    GROUP BY a.year)) c
GROUP BY year;

On your data I get:

   year      points 
    7          14
    8          15
    11         43

Upvotes: 0

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/ad111/7

SELECT tr.`year`,  COALESCE(tr.points,0)+COALESCE(SUM(ar.points),0)
FROM Team_Results tr
LEFT JOIN Students s
ON tr.`year`=s.studentyear
LEFT JOIN Athletics_Results ar
ON s.idStudent = ar.idStudent
GROUP BY tr.year

According to your comment and fiddle provided check http://sqlfiddle.com/#!9/dbc16/3

SELECT tr.`year`,  COALESCE(tr.points,0)+COALESCE(SUM(ar.points),0)
FROM (
  SELECT `year`, SUM(points) as points
  FROM Team_Results
  GROUP BY `year`) tr
LEFT JOIN Students s
ON tr.`year`=s.studentyear
LEFT JOIN Athletics_Results ar
ON s.idStudent = ar.idStudent
GROUP BY tr.year

Upvotes: 1

Related Questions