Re: [sqlite] GROUP BY Problem

Thanks Pavel, that works. Pete Molly's Revenge On Fri, Apr 8, 2011 at 4:36 AM, Pavel Ivanov 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

Re: [sqlite] GROUP BY Problem

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

Re: [sqlite] GROUP BY Problem

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

Re: [sqlite] GROUP BY Problem

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

Re: [sqlite] GROUP BY Problem

From: Pete <p...@mollysrevenge.com> To: sqlite-users@sqlite.org Sent: Fri, April 8, 2011 3:31:05 AM Subject: [sqlite] GROUP BY Problem >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 GROU

[sqlite] GROUP BY Problem

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