我有个这样的表
name yx sy
w1 0.1 0.2
w1 0.9 0.0
w2 0.5 0.6
w2 0.5 0.6
w3 0.8 0.2
w4 0.9 0.1
w5 0.4 0.3我想查询出来这样的结果:
name yx sy
w1 (0.1+0.9)/5 (0.2+0.0)/5
w2 0.5/5 0.6/5
... ... ...
w5 把 所有为w1的 yx sy的值都分别求和 然后在除以 (name 字段下的值有几种就除以几,如:还有w6 就除以6 );
w2到w5也是这样求值!!
name yx sy
w1 0.1 0.2
w1 0.9 0.0
w2 0.5 0.6
w2 0.5 0.6
w3 0.8 0.2
w4 0.9 0.1
w5 0.4 0.3我想查询出来这样的结果:
name yx sy
w1 (0.1+0.9)/5 (0.2+0.0)/5
w2 0.5/5 0.6/5
... ... ...
w5 把 所有为w1的 yx sy的值都分别求和 然后在除以 (name 字段下的值有几种就除以几,如:还有w6 就除以6 );
w2到w5也是这样求值!!
SUM(yx) / (SELECT COUNT(*) FROM (SELECT name FROM 数据表 GROUP BY name) AS N) AS yx,
SUM(sy) / (SELECT COUNT(*) FROM (SELECT name FROM 数据表 GROUP BY name) AS N) AS sy
FROM 数据表
GROUP BY name
name varchar(10),
yx numeric(18,2),
sy numeric(18,2)
)
goinsert into t111 values('w1',0.1,0.2)
insert into t111 values('w1',0.1,0.2)
insert into t111 values('w2',0.1,0.2)
insert into t111 values('w2',0.1,0.2)
insert into t111 values('w3',0.1,0.2)
insert into t111 values('w4',0.1,0.2)
insert into t111 values('w5',0.1,0.2)select name,sum(yx)/(select count( distinct name) FCount from t111 ), sum(sy)/(select count( distinct name) FCount from t111 )
from t111
group by nameSELECT name,
SUM(yx) / (SELECT COUNT(*) FROM (SELECT name FROM t111 GROUP BY name) AS N) AS yx,
SUM(sy) / (SELECT COUNT(*) FROM (SELECT name FROM t111 GROUP BY name) AS N) AS sy
FROM t111
GROUP BY name
name yx sy
select name,(sum(yx)/count(*)) as yx,(sum(sy)/count(*)) as sy from table group by name
SUM(yx) / (SELECT COUNT(*) FROM (SELECT name FROM mytable GROUP BY name) ) AS yx,
SUM(sy) / (SELECT COUNT(*) FROM (SELECT name FROM mytable GROUP BY name) ) AS sy
FROM mytable
GROUP BY name
[Microsoft][ODBC Visual FoxPro Driver]Function name is missing ) 200012 没有这个错误 但查询出来的结果不对啊!!
from t111
group by name这样写是子查询错误!1