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

Adam,

I don't get the correct row counts when running this SQL.
It seems to produce the correct count when there are no rows found,but not when at least
one row is found (I get a much higher count than when running:

SELECT count(*) FROM table2 WHERE date BETWEEN.....AND table1.id = n

,which is the count I want.)

Might be some problem with the case statement? Or with the

from table2, table3
where table2.table1id = table3.table1id

part?

Very grateful for help!

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
>