SQL JOIN+COUNT QUERY

Question

Hi, I'm trying to display a table showing a list of courses. I would like to include a column which shows the number of people who have completed the course and if possible, also the number who have yet to complete it.

My table structure is like so:

Courses
courseid | title | etc

Studying
courseid | studentid | some other fields | has_completed

Ideally the output should be something like this for example:

Course Title | some other fields | Students completed | Students not completed
Something      etc                 5                    3

I'm having difficulty getting the two separate fields counted. Can anyone recommend a good way to do this?

Any advice appreciated, thanks.

Answer 1

Try using the SQL count command

SELECT COUNT(has_completed) FROM Studying WHERE has_completed='1'

Answer 2

Something like this should do:

SELECT c.CourseID, c.Title, SUM(s.Has_Completed) Completed, SUM(1-s.Has_Completed) NotCompleted
FROM Courses c
LEFT JOIN Studying s ON c.CourseID = s.CourseID
GROUP BY c.CourseID, c.Title

Answer 3

If has_completed has value 1 for true and 0 for false, you can solve it very simple:

SELECT c.title, SUM(s.has_completed) as completed, COUNT(studentid) - SUM(s.has_completed) as notCompleted
FROM Courses c, Studying s
where c.courseid = s.courseid
GROUP BY c.courseid, c.title