Saturday, 31 August 2013

how to count in a SQL subquery

how to count in a SQL subquery

I have three tables: Users, Courses, Enrollments. the Enrollments table is
the one that has the foreign keys so I can make Joins, respectively:
users.pk1 = enrollments.u_pk1 and courses.pk1 = enrollments.c_pk1. the
Users table contains both professors and students. so far so good!
What I am trying to do is generate a list of all courses that have in the
name the string 2013, then grab all professors for each course, and
finally get a count of all students in each course from the Enrollment
table which only has the following columns: pk1, c_pk1, u_pk1, role.
this is what I am trying to do but it obviously doesn't work because Count
does not accept sub-queries as an argument:
select c.name, u.name, count(select * from enrollments where role =
'student')
from courses c, users u, enrollments e
where u.pk1 = e.u_pk1 and c.pk1 = e.c_pk1
and c.name like '%2013%' and e.role = 'professor'
order by c.name;
Any hints on how I can make this work the way I want?

No comments:

Post a Comment