Probably this could work: SELECT c1,c2,sum(t2.c3) / count(distinct t3.rowid),count(distinct t3.rowid) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3 on t3.key3=t1.key1 GROUP BY t1.key1
And btw, you didn't say where your c1 and c2 come from but if they are from t1 and t1 has several rows with the same key1 but different c1 and c2 then you will get random data as a result of the query. Pavel On Thu, Apr 7, 2011 at 8:31 PM, 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). > > > 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
