Thanks Pavel, that works. Pete Molly's Revenge <http://www.mollysrevenge.com>
On Fri, Apr 8, 2011 at 4:36 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > 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
