Question 114941 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Exclude current row in average calculation

I have a table with two columns: parties and their respective spendings (party and per_capita). I have to return a table with the column of the parties and the average of all the spedings done by them if the party in the column is not used in the calculation: so, supose I have x, y and z. I want something like:

X | Average of spendings of (Y,Z)
Y | Average of spendings of (X,Z)
Z | Average of spendings of (X,Y)

I tried the following, resulting in NULL for the spending columns:

SELECT pcp.party, avg(pcp.per_capita) OVER (PARTITION BY pcp.party ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS average
FROM per_capita_party pcp
ORDER BY average DESC;
Tags:

3

Answers


  1. 0

    You can use lateral for doing this. For example:

    select distinct symbol,v
from myTable t1, lateral (
   select avg(amount) 
   from myTable t2 where t1.symbol != t2.symbol) t(v);

    Here is DBFiddle demo.

    Login or Signup to reply.
  2. 0

    A scalar subquery will do the job close to natural language.

    SELECT pcp.party,
 (select avg(per_capita) from per_capita_party where party <> pcp.party) average
FROM per_capita_party pcp
ORDER BY average DESC;
    Login or Signup to reply.
  3. 0

    You can also use window function exclude group frame clause.
    Based on Cetin’s DBfilddle.
    new dbfiddle

    SELECT
    symbol,
    avg(amount) OVER (ORDER BY symbol GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
FROM
    mytable;

    or

    SELECT
    symbol,
    avg(amount) OVER (ORDER BY symbol DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
FROM
    mytable;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search