how to count in a SQL subquery

Question

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?

...

later edit: using Trogdor's solution I was able to make it work from the counting point of view but now I am stuck at how to only list a course once followed by all the professors' names in one row. For example, rather having something like this:

course1 prof1 13
course1 prof2 13
course2 prof3 25

instead it should look like:

course1 prof1,prof2 13
course2 prof3 25

and so on... any hints on how I can achieve this?

Which RDBMS (SQL *flavor*) do you use? – PM 77-1 Aug 31 '13 at 23:51 — PM 77-1, Aug 31 '13 at 23:51

score 1 · Accepted Answer · answered Sep 01 '13 at 00:36

1

Try this:

select c.name, u.name, x.total
from courses c, users u, enrollments e, 
    (select c_pk1, count(*) as total 
     from enrollments 
     where role='student' 
     group by c_pk1) x
where u.pk1 = e.u_pk1 and c.pk1 = e.c_pk1 
and c.name like '%2013%' and e.role = 'professor' and c.pk1 = x.c_pk1
order by c.name;

You could also use a correlated subquery in the select:

select c.name, u.name, 
    (select count(*) from enrollments e2 
      where e2.role = 'student' and e2.c_pk1 = c.pk_1) 
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;

however, this second query may be noticeably slower than the first one.

answered Sep 01 '13 at 00:36

trogdor

1,626
14
17

the second one works just great! thanks, trogdor. the only thing I need to deal with now is to not list a course several times if it has two or more professors. how do I fix this: course1 prof1 13 course1 prof2 13. I should only display once the course1 and the count 13 – Don Mob Sep 01 '13 at 01:13
@Don Mob To do that you would need to put all the professors names in a single row. I believe you use Oracle, so you could use `LISTAGG` or other alternatives explained here: http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row – trogdor Sep 01 '13 at 01:43
so I just use `LISTAGG` to list all profs and I also need to put a `DISTINCT` somewhere to only list a course once, right? – Don Mob Sep 01 '13 at 02:39

Metaphor · Answer 2 · 2013-09-02T01:10:27.317

0

I would do it like this. More explicit. Don't have any way to test this, so sorry if there's a bug. But it should give you an idea.

select c.name, p.name, count(*)
from courses c
join enrollments e on e.c_pk1 = c.pk1
join users p on p.pk1 = e.u_pk1 and e.role = 'professor'
join users s on s.pk1 = e.u_pk1 and e.role = 'student'
where c.name like '%2013%'
group by c.name, p.name
order by c.name, p.name

edited Sep 02 '13 at 01:10

answered Aug 31 '13 at 23:40

Metaphor

6,157
10
54
77

So who are you counting? Students, professors, something else? – PM 77-1 Aug 31 '13 at 23:49
PM 77-1, I am trying to list each course, followed by it's professors' names, and a count of students in this course. – Don Mob Sep 01 '13 at 00:08
Metaphor, my Oracle SQLplus shell complains that "enrollments e on" is not proper syntax. also, shouldn't be count(s.pk1) or something similar since you have a join S not used? – Don Mob Sep 01 '13 at 00:11
The count is of all students because the group by makes it so. Look at each join as a separate table, if that will help. Also this way you will only see each course/prof listed only once. Perhaps Oracle requires the INNER keyword. Try to replace JOIN with INNER JOIN. – Metaphor Sep 01 '13 at 07:46
@Metaphor - Remove comma `,` from the end of the second line. – PM 77-1 Sep 01 '13 at 19:35
unfortunately it returns "no rows selected" and I'm suspecting it's because you are joining Courses with Users and Enrollments, instead of joining Enrollments with Courses and Users, right? – Don Mob Sep 02 '13 at 13:58
You'll have to try the joins one at a time and in different configurations to get the hang of it. You know your data. Just play with the joins a bit and you'll "get it". Read the docs on the inner join too. Outer joins are good to know, but not for this problem. – Metaphor Sep 03 '13 at 13:20

how to count in a SQL subquery

2 Answers2