Date: Wed, 15 Apr 2009 21:23:04 -0700
From: Steve Midgley <scie...@misuse.org>
To: Erik Jones <ejo...@engineyard.com>
Subject: Re: How to count from a second table in an aggregate query?
Message-ID: <49e6b2a8.5040...@misuse.org>
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
>
A friend of mine off-list provided an alternative SQL version which I thought
the list might have interest in:
select src_contact_id, count(distinct contact_log.id),
count(distinct contact_property.id)
from
contact_log, contact_property
where contact_log.src_contact_id = contact_property.contact_id
and contact_log.log_type in ('web', 'detail')
group by src_contact_id;
Credit to Matt Gainsborough for that one. Makes perfect sense as I look at it. It's nice
to see two alternate paths to the same solution. ANSI-92 joins work just as well as his
ANSI-89 join syntax for this (i.e. using the "JOIN" keyword to set the
relation).
Steve
