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

Adam Rich wrote:
>> 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

My first thought would be to use a subselect.

select
(select count(table1_ID) from t2
where date > fromdate1 and date < todate1)) as X,
(select count(table1_ID) from t2
where date > fromdate2 and date < todate2)) as Y,
(select count(table1_ID) from t3
where date > fromdate1 and date < todate1)) as Z,
(select count(table1_ID) from t3
where date > fromdate2 and date < todate2)) as V
;

No idea if that's the most efficient, but it is more
intuitive to me. I hadn't really been aware of 'between'.

from
http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html

it seems that

"a BETWEEN x AND y

is equivalent to

a >= x AND a <= y"

Which is wrong (though it may be required by the standard, of course).

1 is not between 1 and 2. "between" shouldn't include the endpoints.

At any rate, the OP will know what he meant by "between" and can select
the appropriate operators.

--
nathan wagner
nw(at)hydaspes(dot)if(dot)org