Yes, I already figured out what the problem was. I was asking for a solution. The SELECT scalar query gets me what I wanted.
Pete Molly's Revenge <http://www.mollysrevenge.com> > Message: 1 > Date: Fri, 8 Apr 2011 08:17:22 -0400 > From: "Igor Tandetnik" <[email protected]> > Subject: Re: [sqlite] GROUP BY Problem > To: [email protected] > Message-ID: <[email protected]> > Content-Type: text/plain; charset="iso-8859-1" > > Pete <[email protected]> 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
