Message-ID: <49E6405D.7010302@misuse.org>
Date: Wed, 15 Apr 2009 13:15:25 -0700
From: Steve Midgley <science@misuse.org>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to count from a second table in an aggregate query?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm trying to figure out how to do something which I'd guess is easy for 
a sql whiz but has me stumped. I would greatly appreciate any help on 
this - it's a form of SQL query that I've never figured out, but have 
wanted to use many times over the years..

I want to generate an analysis report that counts the values in two 
separate tables. I've been able to accomplish what I want with two 
separate queries that I then merge together in Excel. Essentially what I 
need is a "horizontal UNION" statement (or something like that).

I've included some DDL and sample SQL queries that explain what I want 
better than I can in English, but the general idea is:

get a FK id and count of a certain column in one table, based on some 
criteria
 -> for each FK id, get the count of a different column in a different table
Display the counts from both queries side-by-side along with the FK id's 
in a single result set

Thanks for any assistance on this!

Steve

/*SQL STARTS*/
drop table if exists contact_log;
drop table if exists contact_property;
create table contact_log(id serial NOT null, src_contact_id integer, 
log_type character varying(63), CONSTRAINT contact_log_pkey PRIMARY KEY 
(id));
create table contact_property(id serial NOT null, contact_id integer, 
property_id integer,
  CONSTRAINT contact_property_pkey PRIMARY KEY (id), CONSTRAINT 
contact_property_cid_pid UNIQUE (contact_id, property_id));
insert into contact_log (src_contact_id, log_type) values(1, 'web');
insert into contact_log (src_contact_id, log_type) values(1, 'web');
insert into contact_log (src_contact_id, log_type) values(1, 'web');
insert into contact_log (src_contact_id, log_type) values(1, 'detail');
insert into contact_log (src_contact_id, log_type) values(1, 'detail');
insert into contact_log (src_contact_id, log_type) values(2, 'detail');
insert into contact_log (src_contact_id, log_type) values(2, 'detail');
insert into contact_log (src_contact_id, log_type) values(2, 'web');
insert into contact_log (src_contact_id, log_type) values(2, 'foobar');
insert into contact_log (src_contact_id, log_type) values(3, 'foobar');
insert into contact_log (src_contact_id, log_type) values(4, 'web');
insert into contact_property (contact_id, property_id) values(1, 20);
insert into contact_property (contact_id, property_id) values(1, 21);
insert into contact_property (contact_id, property_id) values(1, 22);
insert into contact_property (contact_id, property_id) values(2, 23);
insert into contact_property (contact_id, property_id) values(2, 24);
insert into contact_property (contact_id, property_id) values(1, 50);
insert into contact_property (contact_id, property_id) values(3, 51);
insert into contact_property (contact_id, property_id) values(5, 52);


-- This gets what I want from contact_log
select src_contact_id, count(log_type)
from contact_log
where
contact_log.src_contact_id in (select contact_id from contact_property)
and log_type in ('web', 'detail')
and src_contact_id in (select contact_id from contact_property)
group by src_contact_id
order by src_contact_id;
-- correct output is : 1|5, 2|3

-- This gets what I want from contact_property
select contact_id, count(property_id)
from contact_property
where
contact_id in (select src_contact_id from contact_log where log_type in 
('web', 'detail'))
group by contact_id
order by contact_id;
-- correct output is: 1|4, 2|2

-- THIS DOESN'T WORK (of course - but what would?)
select src_contact_id, count(log_type), count(property_id)
from contact_log
join contact_property cp on cp.contact_id = contact_log.src_contact_id
where
contact_log.src_contact_id in (select contact_id from contact_property)
and log_type in ('web', 'detail')
group by src_contact_id
order by src_contact_id
-- correct output *should be* : 1|5|4, 2|3|2
/*SQL ENDS*/