If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Average excluding current observation within group

07-14-12, 09:39
 christomassey Registered User Join Date: Nov 2009 Posts: 6
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
07-14-12, 11:23
 shammat Registered User Join Date: Nov 2003 Posts: 2,675
 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``` __________________ I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code Tips for good questions: http://tkyte.blogspot.de/2005/06/how...questions.html http://wiki.postgresql.org/wiki/SlowQueryQuestions http://catb.org/esr/faqs/smart-questions.html
07-14-12, 20:26
 christomassey 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
07-14-12, 21:00
 tonkuma Registered User Join Date: Feb 2008 Location: Japan Posts: 2,752
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?

Last edited by tonkuma; 07-14-12 at 21:12.

 Tags averages, current record, exclude, function

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Forum Rules