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). For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count of t3.c4 is 5, the sum column returns 5000 and the count column returns 15. If either of t2 or t3 has no qualifying entries, the calculation for the other table is correct. I guess GROUP BY isn't designed to deal with this type of situation. Can anyone suggest a way to do this? Thanks, Pete _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
