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!
