Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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

  3. #3
    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. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... 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 22:12.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •