Re: JOIN and aggregate problem

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: JOIN and aggregate problem
Date: 2009-02-20 19:15:09
Message-ID: 20090220110056.G70742@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote:

> I have 2 tables T1 and T2
>
> T1 has the columns: D, S, C. The combination of D,S,C is unique.
> T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is
> not unique.
>
> I need to produce the following result for every occurrence of T1:
> D,S,C, COUNT
>
> COUNT is the number of matching D,S,C combinations in T2 where X = true.
> There might be no matching pair in T2 or there might be match but X
> is false.
>
> How can I express this?

Maybe something like one of these barely tested queries?

select d, s, c, sum(case when t2.x then 1 else 0 end)
from t1 left outer join t2 using(d,s,c)
group by d, s, c;

or

select d,s,c,
(select count(*)
from t2
where t2.d=t1.d and t2.s=t1.s and t2.c=t1.c and t2.x)
from t1;

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bob Henkel 2009-02-20 19:23:47 Re: JOIN and aggregate problem
Previous Message Bob Henkel 2009-02-20 19:14:44 Re: JOIN and aggregate problem