I want to find how far each member of a group differed from the average for the rest of the group. The distance is easy, but the mean of the rest of the group is eluding me.

For example:

would give:

I am assuming windowed functions would be the way to go, but I seem to have a complete blank as to how to do this in SQL.

Any suggestions?

CodePudding user response：

based on the mathematical calculation, I think this will be easy way to calculate:

select item, 
((select sum(data) from Table where [group] = T.[group]) - data) /cast(((select count(*) from Table where [group] = T.[group]) - 1) as float)  AS groupMean 
from Table as T

Calculation is sum of all data minus current data divided by count of all data - 1 (current one)

I hope it will help

CodePudding user response：

I'll propose an answer based on mathematical equation.

For a group X,Y and Z, AVG = (X Y Z) / Count that applies to Y Z = (AVG * Count) -X.

Y Z is the sum of the group members without X, now we can simply divide Y Z by Count -1to get the average of Y and Z.

Check the following query.

with stat as
(Select group_, avg(data_) as av,sum(data_) as sm, count(data_) as cn
from MyData
group by group_)

Select MyData.item, MyData.group_,
((stat.av*stat.cn)-MyData.data_) *1.0 / (cn-1) as diff
from stat
inner join MyData
on stat.group_=MyData.group_

See the demo from