In PostgreSQL, Creating and appending a new column

Question

I'd like to get an average per type then join the aggregated table as a new column in the original table. Here's a visualization and code of what I'm attempting to do:

-- The original table --

ID | Cnt | Type
 1    5     A
 1    6     A   
 2    4     B

-- New Table -- 

ID | Cnt | Type | Avg
 1    5     A     5.5
 1    6     A     5.5
 2    4     B     4.0

The code I have written thus far is the following:

select AVG(Cnt)
  from old
group by(type)
right join on old

But, obviously it's not correct since a syntax error is raised. What would be the fix for this? I apologize in advance if my question is similar to an already existing one.

Gordon Linoff · Accepted Answer · 2017-12-20 17:59:46Z

1

Use window functions:

select o.*, avg(cnt) over (partition by type)  
from old o;

answered Dec 20, 2017 at 17:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In PostgreSQL, Creating and appending a new column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related