Re: Getting the count(*) from two tables and two date ranges in same query

Hi Adam and all,

I don't get it=). How do I input the second daterange in this query?
Also, I have the ID from table1. Its known in the query.

Wouldn't I need to use a UNION for this kind of query?

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22

-----Ursprungligt meddelande-----
Från: Adam Rich [mailto:adam(dot)r(at)sbcglobal(dot)net]
Skickat: den 28 januari 2008 15:22
Till: Håkan Jacobsson; pgsql-general(at)postgresql(dot)org
Ämne: RE: [GENERAL] Getting the count(*) from two tables and two date ranges in same query

> Resulting in 4 columns in the ResultSet like:
>
> count(*)_from_table2_between_fromdate1_and_todate1 = X
> count(*)_from_table2_between_fromdate2_and_todate2 = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*)_from_table3_between_fromdate2_and_todate2 = V
>
> Is this possible?

Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1,
sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id