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@hydaspes.if.org