From: | Håkan Jacobsson <hakan(dot)jacobsson(at)relevanttraffic(dot)com> |
---|---|
To: | Adam Rich <adam(dot)r(at)sbcglobal(dot)net>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Getting the count(*) from two tables and two date ranges in same query |
Date: | 2008-01-29 09:06:19 |
Message-ID: | 5580CB5EB883C44587BC48FA0E54592223E4E14665@RTSRV02.relevanttraffic.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Adam,
Big thanx - I'll try this out!
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 16:27
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
Håkan,
You can add as many date ranges as you need:
Select t1.id,
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t1, t2, t3
where t1.id=t2.id and t2.id = t3.id
group by t1.id
If you know the ID, you can modify slightly:
Select
sum(case when t2.date between d1 and d2 then 1 else 0
end) as sum1,
sum(case when t2.date between d3 and d4 then 1 else 0
end) as sum2
sum(case when t3.date between d1 and d2 then 1 else 0
end) as sum3,
sum(case when t3.date between d3 and d4 then 1 else 0
end) as sum4
from t2, t3
where t2.id = t3.id
and t2.id = 123456
--- Håkan Jacobsson
<hakan(dot)jacobsson(at)relevanttraffic(dot)com> wrote:
> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
> choose an index scan if your joining column's
> datatypes do not
> match
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Bauer | 2008-01-29 09:13:01 | bytea to varchar using different charsets |
Previous Message | Florian Weimer | 2008-01-29 08:10:13 | Re: [GENERAL] SHA1 on postgres 8.3 |