From: "Colin Fox" <cfox@cfconsulting.ca>
Organization: CF Consulting Inc.
Subject: Multiple counts
User-Agent: Pan/0.13.2 (I wwwondeling why alla boppah ferra pushing in?)
Message-ID: <pan.2002.12.29.04.55.08.472104@cfconsulting.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Lines: 49
Date: Sun, 29 Dec 2002 04:55:12 GMT
To: pgsql-sql@postgresql.org

Hi, all. 

I'm trying to calculate two count()s.

I have a campaigns table, a campaign_parts table and a people table.

Here is a simplified view of 3 tables I'm trying to use:

create table campaigns {id serial, company_id int, name varchar(20));
create table campaign_parts(id serial, campaign_id int, name varchar(20));
create table people (id serial, campaignpart_id int, name varchar(20));

(fk references ignored for brevity).


I'd like to create a query which shows how many campaign parts there are
for a given set of campaigns, and how many people signed up via a campaign
(which means a sum of all the signups under all the parts of that
campaign).

The naive way to do this would be something like:
select
    camp.id,
    camp.name,
    count(cp.id) as numparts,
    count(p.id) as numsignups
from
    campaigns camp,
    campaign_parts cp,
    people p
where
    camp.company_id = <some value> and
    cp.campaign_id = camp.id and
    p.cpid = cp.id
group by
    camp.id, camp.name;

(I know this is really naive, but I hope it shows what I'm trying to do).

However, there are some problems with this. First, if there are no
campaign parts, I'd like to show a 0, but then that campaign doesn't
show up at all. So I need to UNION it with another selection.

Also, the counting doesn't seem to be working. I'm getting a multiple of
the correct answer.

Please help!