# Thread: Average excluding current observation within group

1. Registered User
Join Date
Nov 2009
Posts
6

## Unanswered: Average excluding current observation within group

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

2. Registered User
Join Date
Nov 2003
Posts
2,988
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```

3. Registered User
Join Date
Nov 2009
Posts
6
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

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
... perhaps I was not clear:
It's not the matter expressed by words"not clear".

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?
Last edited by tonkuma; 07-14-12 at 22:12.