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
– PM 77-1

2013-08-31 23:51:10 +00:00
Commented Aug 31, 2013 at 23:51 — PM 77-1
– PM 77-1, Commented Aug 31, 2013 at 23:51

trogdor · Accepted Answer · 2013-09-01 00:36:37Z

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 1, 2013 at 0:36

trogdor

1,65614 silver badges17 bronze badges

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

3 Comments

Don Mob Over a year ago

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

trogdor Over a year ago

@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: stackoverflow.com/questions/1120706/…

Don Mob Over a year ago

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?

Metaphor · Accepted Answer · 2013-09-02 01:10:27Z

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 2, 2013 at 1:10

answered Aug 31, 2013 at 23:40

Metaphor

6,42512 gold badges58 silver badges81 bronze badges

7 Comments

PM 77-1 Over a year ago

So who are you counting? Students, professors, something else?

Don Mob Over a year ago

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 Over a year ago

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?

Metaphor Over a year ago

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.

PM 77-1 Over a year ago

@Metaphor - Remove comma , from the end of the second line.

|

Collectives™ on Stack Overflow

how to count in a SQL subquery

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related