Pete <p...@mollysrevenge.com> wrote:
> I am trying to use GROUP BY to summarise information from a main table and
> two sub tables, e.g.:
>
> SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1
> LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1
>
> The result is that the count column returns the count of (the number of t2
> entries * the number of t3 entries), and the sum column returns (the t2 sum
> value * the count of entries in t3).
Of course - you are doing your sums and counts on a cartesian product of these
two tables. I suspect you want
select c1, c2,
(select sum(c3) from t2 where key2 = t1.key1),
(select count(c4) from t3 where key3 = t1.key1)
from t1;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
