From: Steve Midgley <science@misuse.org>
To: pgsql-sql@postgresql.org
Subject: How to count from a second table in an aggregate query?
Date: Wed, 15 Apr 2009 13:15:25 -0700
Message-ID: <49E6405D.7010302@misuse.org> (raw)
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*/
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: science@misuse.org
Subject: Re: How to count from a second table in an aggregate query?
In-Reply-To: <49E6405D.7010302@misuse.org>
* 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