Re: [sqlite] GROUP BY Problem

[Pete]

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 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  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

Re: [sqlite] GROUP BY Problem

[Jim Morris]

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

Re: [sqlite] GROUP BY Problem

[Igor Tandetnik]

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).

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY Problem

[Pavel Ivanov]

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  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

Re: [sqlite] GROUP BY Problem

[Marian Cascaval]

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 GROUP BY t1.key1

Shouldn't the second JOIN be 

LEFT JOIN t3 ON t3.key3=t1.key1 ?


Marian Cascaval
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GROUP BY Problem

[Pete]

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