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