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.

You can also spell Colour with a u ;)

Mike
– Mike

2009-04-08 19:09:22 +00:00
Commented Apr 8, 2009 at 19:09 — Mike
– Mike, Commented Apr 8, 2009 at 19:09

David M · Accepted Answer · 2009-04-08 09:24:38Z

8

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.

answered Apr 8, 2009 at 9:24

David M

73.2k13 gold badges164 silver badges187 bronze badges

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

Comments

Marc Gravell · Accepted Answer · 2009-04-08 09:25:20Z

2

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

SELECT COUNT(DISTINCT STUDENT) FROM COURSE_ENROLMENT

answered Apr 8, 2009 at 9:25

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

campo · Accepted Answer · 2009-04-08 09:29:03Z

2

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

answered Apr 8, 2009 at 9:29

campo

1,9421 gold badge17 silver badges19 bronze badges

Comments

soulmerge · Accepted Answer · 2009-04-08 09:24:46Z

1

SELECT COUNT(DISTINCT STUDENT) FROM COURSE_ENROLMENT

answered Apr 8, 2009 at 9:24

soulmerge

76.2k20 gold badges121 silver badges160 bronze badges

Comments

Anton Gogolev · Accepted Answer · 2009-04-08 09:27:26Z

1

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

answered Apr 8, 2009 at 9:27

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

Comments

user80168 · Accepted Answer · 2009-04-08 17:34:06Z

1

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.

answered Apr 8, 2009 at 17:34

user80168

Comments

sree · Accepted Answer · 2016-03-11 13:09:57Z

0

select COUNT(distinct student) from course_enrolment

edited Mar 11, 2016 at 13:09

answered Feb 20, 2015 at 12:37

sree

1,9701 gold badge22 silver badges37 bronze badges

Collectives™ on Stack Overflow

Simple SQL to return a count without using sub queries

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related