Erik Jones wrote:
On Apr 15, 2009, at 1:15 PM, Steve Midgley wrote:
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).
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
Joining against a subquery for the second count does the trick:
select src_contact_id, count(log_type), cp.count
from contact_log ,
(select contact_id, count(property_id)
from contact_property
group by contact_id) as cp
where src_contact_id = cp.contact_id
and log_type in ('web', 'detail')
group by src_contact_id, cp.count
order by src_contact_id
src_contact_id | count | count
----------------+-------+-------
1 | 5 | 4
2 | 3 | 2
Thanks Erik! This is perfect. Oliveiros showed me another neat solution
a while back that involved a select statement in the from clause, but I
kind filed that solution mentally as a cool parlor trick. Now I see that
I'm going to have to learn and study this form of SQL more closely, as
it's extremely flexible and powerful.
Thanks for the very complete and patiently instructive response - it
makes perfect sense. I'll work to share this along as I go.
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
