Re: How to count from a second table in an aggregate query?

Date: Wed, 15 Apr 2009 21:23:04 -0700
From: Steve Midgley <science(at)misuse(dot)org>
To: Erik Jones <ejones(at)engineyard(dot)com>
Subject: Re: How to count from a second table in an aggregate query?
Message-ID: <49E6B2A8(dot)5040308(at)misuse(dot)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