On Sat, 2 Nov 2013 18:06:30 +0100
Gert Van Assche <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
