Dumb question about count()

From: Benjamin Smith <lists(at)benjamindsmith(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Dumb question about count()
Date: 2005-07-22 05:09:07
Message-ID: 200507212209.07646.lists@benjamindsmith.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm sure I've done this before, but for some reason, my main noodle is drawing
a blank. Assume we have three tables defined thusly:

create table classrooms (
id serial unique not null,
name varchar
);

create table seats (
classrooms_id integer not null references classrooms(id),
position varchar
);

create table students(
classrooms_id integer not null references classrooms(id),
name varchar
);

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

Except that it counts 0s for seats/students.

Why can't I recall/find how to do this particular join?

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2005-07-22 05:14:44 Re: Strange input/cast semantics for inet
Previous Message Michael Fuhr 2005-07-22 03:26:56 Re: Strange input/cast semantics for inet