From: Colin Fox <cfox@cfconsulting.ca>
To: pgsql-sql@postgresql.org
Subject: Multiple counts
Date: Sun, 29 Dec 2002 04:55:12 GMT
Message-ID: <pan.2002.12.29.04.55.08.472104@cfconsulting.ca> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cfox@cfconsulting.ca
Subject: Re: Multiple counts
In-Reply-To: <pan.2002.12.29.04.55.08.472104@cfconsulting.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox