Did you want to use correlated sub queries? Something like: SELECT c1,c2, (select sum(t2.c3) FROM t2 WHERE t2.key2=t1.key1) as mySum, (select count(t3.c4) FROM t3 where t3.key3=t1.key1) as myCount FROM t1 ;
On 4/7/2011 5:31 PM, Pete 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). > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
