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

[Håkan Jacobsson <hakan.jacobsson@xxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx]
Skickat: den 28 januari 2008 16:27
Till: Håkan Jacobsson; pgsql-general@xxxxxxxxxxxxxx
Ämne: Re:  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@xxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx]
> Skickat: den 28 januari 2008 15:22
> Till: Håkan Jacobsson; pgsql-general@xxxxxxxxxxxxxx
> Ämne: RE:  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
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings