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

<pre wrap="">Date: Wed, 15 Apr 2009 21:23:04 -0700
From: Steve Midgley <a class="moz-txt-link-rfc2396E" href="mailto:science@misuse.org"><science@misuse.org></a>
To: Erik Jones <a class="moz-txt-link-rfc2396E" href="mailto:ejones@engineyard.com"><ejones@engineyard.com></a>
Subject: Re: How to count from a second table in an aggregate query?
Message-ID: <a class="moz-txt-link-rfc2396E"
href="mailto:49E6B2A8.5040308@misuse.org"><49E6B2A8.5040308@misuse.org></a>

Erik Jones wrote:
</pre><blockquote type="cite"><pre wrap=""><span class="moz-txt-citetags">></span>
<span class="moz-txt-citetags">> </span>On Apr 15, 2009, at 1:15 PM, Steve Midgley wrote:
<span class="moz-txt-citetags">></span> </pre><blockquote type="cite"><pre wrap=""><span
class="moz-txt-citetags">>></span>I want to generate an analysis report that counts the values in two 
 
<span class="moz-txt-citetags">>> </span>separate tables. I've been able to accomplish what I want with two 
<span class="moz-txt-citetags">>> </span>separate queries that I then merge together in Excel. Essentially 
<span class="moz-txt-citetags">>> </span>what I need is a "horizontal UNION" statement (or something like that).
<span class="moz-txt-citetags">>></span>
<span class="moz-txt-citetags">>> </span>get a FK id and count of a certain column in one table, based on some 
<span class="moz-txt-citetags">>> </span>criteria
<span class="moz-txt-citetags">>> </span>-> for each FK id, get the count of a different column in a different

<span class="moz-txt-citetags">>> </span>table
<span class="moz-txt-citetags">>> </span>Display the counts from both queries side-by-side along with the FK 
<span class="moz-txt-citetags">>> </span>id's in a single result set   </pre></blockquote><pre wrap=""><span
class="moz-txt-citetags">></span>
<span class="moz-txt-citetags">> </span>Joining against a subquery for the second count does the trick:
<span class="moz-txt-citetags">></span>
<span class="moz-txt-citetags">> </span>select src_contact_id, count(log_type), cp.count
<span class="moz-txt-citetags">> </span>from contact_log ,
<span class="moz-txt-citetags">> </span>    (select contact_id, count(property_id)
<span class="moz-txt-citetags">> </span>      from contact_property
<span class="moz-txt-citetags">> </span>      group by contact_id) as cp
<span class="moz-txt-citetags">> </span>where src_contact_id = cp.contact_id
<span class="moz-txt-citetags">> </span>    and log_type in ('web', 'detail')
<span class="moz-txt-citetags">> </span>group by src_contact_id, cp.count
<span class="moz-txt-citetags">> </span>order by src_contact_id
<span class="moz-txt-citetags">></span>
<span class="moz-txt-citetags">> </span> src_contact_id | count | count
<span class="moz-txt-citetags">> </span>----------------+-------+-------
<span class="moz-txt-citetags">> </span>              1 |     5 |     4
<span class="moz-txt-citetags">> </span>              2 |     3 |     2
<span class="moz-txt-citetags">></span> </pre></blockquote><pre wrap="">A friend of mine off-list provided an
alternativeSQL 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
thesame solution. ANSI-92 joins work just as well as his ANSI-89 join syntax for this (i.e. using the "JOIN" keyword to
setthe relation).
 

Steve


</pre>