Re: Dumb question about count() - Mailing list pgsql-general

Benjamin Smith wrote:
> Now, I want to get a result like:
>
> classroom | students | seats
> 101A     0    25
> 101B    22    30
> 102A    11    0
> ... etc.
>
> Something somewhat akin to
>
> select classroom.title,
>     count(students.id) AS students,
>     count(seats.id) AS seats
> from classrooms, students, seats
> where classrooms.id=students.classrooms_id
> and classrooms.id=seats.id

try

select classroom.title,
    (select count(*) from students
     where classrooms_id = c.id) AS students,
    (select count(*) from seats
     where classrooms_id = c.id) AS students,
    count(seats.id) AS seats
from classrooms c

Cheers,

Eze