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@xxxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
