Exclude current row in average calculation-CodePudding

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;

CodePudding user response：

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.

CodePudding user response：

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;