Dumb question about count()

Поиск
Список
Период
Сортировка
От Benjamin Smith
Тема Dumb question about count()
Дата
Msg-id 200507212209.07646.lists@benjamindsmith.com
обсуждение исходный текст
Ответы Re: Dumb question about count()  (Michael Fuhr <mike@fuhr.org>)
Re: Dumb question about count()  (Ezequiel Tolnay <mail@etolnay.com.ar>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Fuhr
Дата:
Сообщение: Re: Strange input/cast semantics for inet
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Strange input/cast semantics for inet