Re: Getting the count(*) from two tables and two date ranges in same query - Mailing list pgsql-general

=?iso-8859-1?Q?H=E5kan_Jacobsson?= <hakan.jacobsson@relevanttraffic.com> writes:
> I just realised that issuing the SQL on one table produces the correct count.

> SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1 else 0
> end) as sumx FROM table2 WHERE id = n;

> This is working alright.
> So the problem should lie in the last part:

> from table2, table3
> where table2.id = table3.id
> and table2.id = n;

It sounds like there are multiple rows in table3 matching the id of (at
least some of) the rows in table2.  You'll get an increment to the sum
for each join pair.

            regards, tom lane