On Sat, 2 Nov 2013 18:06:30 +0100 Gert Van Assche <ger...@datamundi.be> wrote:
> CREATE TABLE T (N, V, G); > INSERT INTO T VALUES('a', 1, 'x'); > INSERT INTO T VALUES('b', 3, 'x'); > INSERT INTO T VALUES('c', null, 'x'); > INSERT INTO T VALUES('d', 80, 'y'); > INSERT INTO T VALUES('e', null, 'y'); > INSERT INTO T VALUES('f', 60, 'y'); > INSERT INTO T VALUES('g', null, 'y'); > INSERT INTO T VALUES('h', null, 'z'); > INSERT INTO T VALUES('i', 111, 'z'); > > I would like to see where N='c', V as the average for the group (G) > were this record belongs to (so 'x'). So, you want the average of the non-missing V per G: sqlite> select G, avg(V) as avgV from T group by G; G avgV ---------- ---------- x 2.0 y 70.0 z 111.0 and to see that average for each N that belongs to G: sqlite> select T.N, A.* from T join (select G, avg(V) as avgV from T sqlite> group by G) as A on T.G = A.G; N G avgV ---------- ---------- ---------- a x 2.0 b x 2.0 c x 2.0 d y 70.0 e y 70.0 f y 70.0 g y 70.0 h z 111.0 i z 111.0 --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users