I have a table:

I want to add a new column at the end which has the average number of likes of each post's uploader. So if James has average 19 likes, I want both rows 1 and 2 to have 19 in them.

I am trying this:

update mytable A
set    "AverageLikesOfUploader" = 
(
   select avg(B.Likes)
   from   mytable B
   group by B.Uploader
)

But it didn't work.

What should I do to get resultant this:

CodePudding user response：

What you do is right.

If you want a new column, after the update that you did, you have to distinct your results:

SELECT Uploader, AverageLikesOfUploader FROM yourtable GROUP BY Uploader, AverageLikesOfUploader

But in any case you have to still update your data everytime. It has no sense to me. Why do you want to save in a table a calculated value? I suggest you the following way:

If you don't want to create a new column:

SELECT 
Uploader, 
avg(B.Likes) as AverageLikesOfUploader
FROM
yourtable
GROUP BY Uploader

CodePudding user response：

There needs to be a relation between A and B in the query of the update statement.

update mytable A
set AverageLikesOfUploader = (
   select avg(B.Likes)
   from mytable B
   where B.Uploader = A.Uploader
)

db<>fiddle here

CodePudding user response：

The easiest way to do this is to use avg over a window

select *, Avg(likes) over(partition by uploader)::int AverageLikesOfUploader
from t

If you really want to update your table with the average (this is not a good idea, just use the above query to get the average) you can apply it with a CTE

with x as (
 select *, Avg(likes) over(partition by uploader) av
 from t
)
update t set AverageLikesOfUploader=av
from x
where x.PostId=t.PostId;

See this demo fiddle