Average excluding current observation within group

christomassey · #1 (**permalink**) 07-14-12, 09:39

Hi everybody, thank you in advance for any input.

I am trying to calculate the average score of a group that a an entity belongs to without including the score of that particular entity, but recalculate the average for each member of the group for each time period.

Suppose I have a table that looked like this

year ID score
'91 01 0.3
'91 02 0.1
'91 03 0.2
'92 01 0.5
'92 02 0.1
'92 03 0.0
'93 01 0.2
'93 02 0.3
'93 03 0.1

I would like to calculate the average score of the other members in that year so that my table would look like this after the calculation

year ID score avg
'91 01 0.3 0.15
'91 02 0.1 0.25
'91 03 0.2 0.2
'92 01 0.5 0.05
'92 02 0.1 0.25
'92 03 0.0 0.3
'93 01 0.2 0.2
'93 02 0.3 0.15
'93 03 0.1 0.25

Thank you again

shammat · #2 (**permalink**) 07-14-12, 11:23

Code:

select year, 
       id,
       (select avg(score) from groups g2 where g2.year = g1.year and g2.id <> g1.id) as avg_score
from groups g1

christomassey · #3 (**permalink**) 07-14-12, 20:26

Sorry I apologize, perhaps I was not clear:

The table ( for the sake of the argument is called 'abc') contains the score of many individuals over many years that belong to different clusters.

eg.

Table abc

year id cluster score
1991 01 a 1
1991 02 a 2
1991 03 a 3
1991 04 b 5
1991 05 b 8
1991 06 c 4
1992 01 a 6
1992 02 a 3
1992 03 a 5
1992 04 b 9
1992 05 b 4
1992 06 c 1
1993 01 a 2
1993 02 a 3
1993 03 a 6
1993 04 b 5
1993 05 b 4
1993 06 c 1
1993 07 c 2

I am looking for SQL code that will group by year and cluster and then calculate the avg(score) of each cluster without including the score of that particular id.

I hope to create a table that looks like this

year id group score AVG*
1991 01 a 1 2.5
1991 02 a 2 2
1991 03 a 3 1.5
1991 04 b 5 8
1991 05 b 8 5
1991 06 c 4
1992 01 a 6 4
1992 02 a 3 5.5
1992 03 a 5 4.5
1992 04 b 9 4
1992 05 b 4 9
1992 06 c 1
1993 01 a 2 4.5
1993 02 a 3 4
1993 03 a 6 2.5
1993 04 b 5 4
1993 05 b 4 5
1993 06 c 1 2
1993 07 c 2 1

The AVG* column represents the average score of the other members of the group in that year ( not including the score of the id for which the average is calculated.

Thank you shammat for your effort, I apologize if I was not clear the first time, your help is appreciated

Regards

tonkuma · #4 (**permalink**) 07-14-12, 21:00

Quote:

... perhaps I was not clear:

It's not the matter expressed by words"not clear".
You have changed your problem.

Anyway, shammat's solution would be applicable to your new problem.
Just add (condition for, or column name of) cluster in the places where year was used.

By the way,
do you want to rename a column name cluster to group?

Internet Services

Web Development

Digital Marketing

Consumer Tech