Re: Getting the count(*) from two tables and two date ranges in same query
От | Håkan Jacobsson |
---|---|
Тема | Re: Getting the count(*) from two tables and two date ranges in same query |
Дата | |
Msg-id | 5580CB5EB883C44587BC48FA0E54592223E4E14665@RTSRV02.relevanttraffic.local обсуждение исходный текст |
Ответ на | Re: Getting the count(*) from two tables and two date ranges in same query (Håkan Jacobsson <hakan.jacobsson@relevanttraffic.com>) |
Список | 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.r@sbcglobal.net] Skickat: den 28 januari 2008 16:27 Till: Håkan Jacobsson; pgsql-general@postgresql.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.jacobsson@relevanttraffic.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.r@sbcglobal.net] > Skickat: den 28 januari 2008 15:22 > Till: Håkan Jacobsson; pgsql-general@postgresql.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 >
В списке pgsql-general по дате отправления: