From: Steve Midgley <science@misuse.org>
To: pgsql-sql@postgresql.org, Erik Jones <ejones@engineyard.com>
Subject: Re: How to count from a second table in an aggregate query?
Date: Fri, 17 Apr 2009 11:36:37 -0700
Message-ID: <49E8CC35.7040808@misuse.org> (raw)
In-Reply-To: <20090417002017.CCFA21E8235E@mx2.hub.org>
References: <20090417002017.CCFA21E8235E@mx2.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: science@misuse.org, ejones@engineyard.com
Subject: Re: How to count from a second table in an aggregate query?
In-Reply-To: <49E8CC35.7040808@misuse.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox