From: Steve Midgley <science@misuse.org>
To: Erik Jones <ejones@engineyard.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to count from a second table in an aggregate query?
Date: Wed, 15 Apr 2009 21:23:04 -0700
Message-ID: <49E6B2A8.5040308@misuse.org> (raw)
In-Reply-To: <F5F5A9C4-A636-47F1-8DA3-49D14AB0FD07@engineyard.com>
References: <49E6405D.7010302@misuse.org>
<F5F5A9C4-A636-47F1-8DA3-49D14AB0FD07@engineyard.com>
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
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: <49E6B2A8.5040308@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