Mysql JOIN / SUM Query

Question

I have 2 tables:

courses: a list of open courses
student_courses: students who have signed up for a course

Tey are described below:

courses:
id
name
approved_by

student_courses:
id
student_id
course_id
paid

I want to list all courses with the following for each course: course id and the number of students signed up for that course. I've tried things like:

SELECT courses.id, SUM(student_courses.id)
FROM courses as courses
LEFT JOIN student_courses as student_courses on student_courses.course_id=courses.id
WHERE courses.approved_by != '0'

But that is only returning 1 row.

You have no GROUP BY clause, needed for the aggregate SUM(). — Michael Berkowski
– Michael Berkowski, Commented Jan 2, 2012 at 23:42
In your query, you are using a table 'students' that you didn't define in your schema description. You are also joining the students table onto an 'instructor_id' field, that both wasn't defined and doesn't make sense to join to students. Could you clarify this, as there seem to be other variables here that might throw off anyone trying to help you out. — John
– John, Commented Jan 2, 2012 at 23:47
Students first & last name was just a join, but I removed them for simplicity! — execv
– execv, Commented Jan 2, 2012 at 23:49
If course_id refers to a section of a course, then you probably do not need the "id" field on the "student_courses" table. In that case a composite key of "student_id" and "course_id" should be enough to ensure uniqueness. — Aaron
– Aaron, Commented Jan 2, 2012 at 23:55

ypercubeᵀᴹ · Accepted Answer · 2012-01-03 00:15:46Z

3

SELECT courses.id
     , courses.name
     , COUNT(student_courses.id) AS number_of_students    ---COUNT(), not SUM()
FROM courses 
  LEFT JOIN student_courses  
    ON student_courses.course_id = courses.id
WHERE courses.approved_by <> '0'
GROUP BY courses.id

HAVING COUNT(student_courses.id) < courses.student_slots

edited Jan 3, 2012 at 0:15

answered Jan 2, 2012 at 23:57

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

execv Over a year ago

Thanks! One last question: How can I use number_of_students in my WHERE (for example, I want to only grab the courses that are not full). I tried modifying my where to be: WHERE courses.approved_by <> '0' AND number_of_students < courses.student_slots But I get: "Unknown column 'number_of_students' in 'where clause'"

ypercubeᵀᴹ Over a year ago

See my edit. Such conditions (that involve values that are calculated during GROUP BY should be put in HAVING clause)

Collectives™ on Stack Overflow

Mysql JOIN / SUM Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related