Multiple counts

From: Richard Rowell <rwrowell(at)bellsouth(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Multiple counts
Date: 2001-12-27 05:36:05
Message-ID: 20011227053930.DMEM18093.imf07bis.bellsouth.net@there
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a one-to-many relationship with a table structure like so:

create table call(
uid serial,
store int,
...)

create table status(
call_uid int,
status_uid int,
...)

First off, please note that the "call" table will get large ( growing by up
to 6k records per week) and the status table will grow linearly with it (n*20
or so).

With that in mind, what I am trying for is an efficient query to get a tuple
set like so:
store number_of_calls number_of_status_where_statusuid_iszero

I can get 1&2:
select store, count(*) from call group by store;

or 1&3:
select c.store,count(s.status_uid)
from call c, status s
where s.call_uid=c.uid
and s.status_uid=0
group by c.store

But all my lame attempts at getting both in the same query fail.
TIA
_________________________
Richard Rowell
rwrowell(at)bellsouth(dot)net

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message andrea 2001-12-27 20:28:43 left join and where
Previous Message Tom Lane 2001-12-27 04:54:00 Re: won't drop the view