Thanks James -- the select query is something I could do, but the update
one I could not get that one right.
I was considering to create a new table based on the select query, but
since the real data set is millions of records, an update was the best
solution.
As always, thanks for your help.
gert
2013/11/3 James K. Lowden <jklow...@schemamania.org>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
