Message-ID: <49E6B2A8.5040308@misuse.org>
Date: Wed, 15 Apr 2009 21:23:04 -0700
From: Steve Midgley <science@misuse.org>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Erik Jones <ejones@engineyard.com>
CC: pgsql-sql@postgresql.org
Subject: Re: How to count from a second table in an aggregate query?
References: <49E6405D.7010302@misuse.org>
	<F5F5A9C4-A636-47F1-8DA3-49D14AB0FD07@engineyard.com>
In-Reply-To: <F5F5A9C4-A636-47F1-8DA3-49D14AB0FD07@engineyard.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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