Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B3C646330F1 for ; Fri, 17 Apr 2009 15:36:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97469-01 for ; Fri, 17 Apr 2009 15:36:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from starfish.geekisp.com (mail.geekisp.com [216.168.135.169]) by mail.postgresql.org (Postfix) with ESMTP id E0755632C49 for ; Fri, 17 Apr 2009 15:36:43 -0300 (ADT) Received: (qmail 19629 invoked by uid 1003); 17 Apr 2009 18:36:41 -0000 Received: from localhost (HELO ?10.21.10.111?) (public@misuse.org@127.0.0.1) by mail.geekisp.com with SMTP; 17 Apr 2009 18:36:41 -0000 Message-ID: <49E8CC35.7040808@misuse.org> Date: Fri, 17 Apr 2009 11:36:37 -0700 From: Steve Midgley User-Agent: Thunderbird 2.0.0.21 (Windows/20090302) MIME-Version: 1.0 To: pgsql-sql@postgresql.org, Erik Jones Subject: Re: How to count from a second table in an aggregate query? References: <20090417002017.CCFA21E8235E@mx2.hub.org> In-Reply-To: <20090417002017.CCFA21E8235E@mx2.hub.org> Content-Type: multipart/alternative; boundary="------------040909060306070102060406" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/104 X-Sequence-Number: 32450 This is a multi-part message in MIME format. --------------040909060306070102060406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Date: Wed, 15 Apr 2009 21:23:04 -0700 From: Steve Midgley To: Erik Jones Subject: Re: How to count from a second table in an aggregate query? Message-ID: <49E6B2A8.5040308@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 --------------040909060306070102060406 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

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

--------------040909060306070102060406--