CODEWITHSUNDEEP
mysqlsqljoinsubquery
christianleroy
christianleroy

Reputation: 1104

SUM of a column from a second degree join?

I'm trying to query 3 tables: students, template_subjects, and subjects.

The students and template_subjects tables have a template_code column, so I can join them through this column (which I think is a pivot table between a template_codes table and the subjects table. This isn't a database I designed, just querying it now).

Now, I need to get the sum of the units column from the subjects table grouped by the template_code. How can I do it?

Here's what I've tried so far:

SELECT 
s.studNo as 'Student Number',
s.lastName as 'Student Name',
s.template_code as 'Template Code',
(SELECT sum(units) FROM subjects
JOIN template_subjects ON subjects.subject_code=template_subjects.subject_code
GROUP BY s.template_code) as 'Units'
FROM students as s
JOIN template_subjects ON template_subjects.template_code=s.template_code

What I expect to get from the query is, for example:

Student Number | Student Name | Template Code | Units
201555101        Jane Doe       ACC301          35

Where Units is the total units of subjects inside a certain template code. (Table names and columns have much more complex names, so I simplified them in this question. Please inform me of any errors or confusing parts that I may have written in this question.)

Upvotes: 1

Views: 52

Answers (2)

christianleroy
christianleroy

Reputation: 1104

If anyone needs the answer, here is how I made it work:

SELECT 
s.studNo as 'Student Number',
s.lastName as 'Student Name',
s.template_code as 'Template Code',
(SELECT sum(units) 
FROM template_subjects
JOIN subjects ON template_subjects.subject_code=subjects.subject_code
WHERE template_subjects.templade_code=s.template_code) as 'Units'
FROM students as s
JOIN template_subjects ON template_subjects.template_code=s.template_code

Please note this difference in the subquery:

WHERE template_subjects.templade_code=s.template_code

instead of:

GROUP BY s.template_code

And also, instead of querying the subjects table itself in the subquery, I queried the template_subjects instead and joined the subjects table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want something like this:

SELECT s.studNo as `Student Number`, s.lastName as `Student Name`,
       s.template_code as `Template Code`,
       (SELECT sum(ts.units)
        FROM template_subjects ts  
        WHERE s.subject_code = ts.subject_code
       ) as Units
FROM students s;

That is, you just need a correlated subquery and not quite so many joins.

Upvotes: 1

Related Questions