Simple SQL to return a count without using sub queries

Question

I am trying to get the number of students enrolled in courses via a single SQL statement, but not using sub-queries. So far I can only figure out how to do it using sub-queries. Is there another way?

Consider the following database setup:

create table student (id integer not null primary key);
create table course_enrolment (student integer not null references student, course integer not null);
insert into student values (1);
insert into student values (2);
insert into student values (3);
insert into course_enrolment values (2,20);
insert into course_enrolment values (2,30);
insert into course_enrolment values (3,10);

I want to get the number of students enrolled in courses. In this case, it's 2.

I can achieve this easily using sub-queries:

SELECT COUNT(*) FROM (SELECT DISTINCT STUDENT FROM COURSE_ENROLMENT) AS DATA;

I want to get the count without using a sub-query.

I am using Postgresql 8.3 but am looking for a vendor agnostic solution.

Answer 1

How about this:

SELECT  course, COUNT(DISTINCT student)
FROM    course_enrolment
GROUP BY course

This gives students per course. If you just want the total number enrolled on any course:

SELECT  COUNT(DISTINCT student)
FROM    course_enrolment

I believe that's all ANSI-standard SQL, so should work most places.

Answer 2

SELECT COUNT(DISTINCT STUDENT) FROM COURSE_ENROLMENT

Answer 3

I don't know about postgres, but on SQL Server:

SELECT COUNT(DISTINCT STUDENT) FROM COURSE_ENROLMENT

Answer 4

This is to select students-by-course:

select course, count(*) as students 
from course_enrolment
group by course

and this is just to count students (hopefully not terrible SQL Server-specific)

select count(distinct student) from course_enrolment

Answer 5

I don't know much about Postgresql but this is how I would do it MS SQL Server...

select count(distinct student)
From course_enrolment

Answer 6

The real question is why don't you want to use subquery?

Generally everybody answered in correct way:

select count(distinct student) from course_enrolment

but this type of query in pre-8.4 PostgreSQL will be slow for large numbers of students/courses. Much faster way would be:

select count(*) from (select student from course_enrolment group by student);

but it uses subquery - which, for some reason, you don't want.