What Is The SQL Equivalent To Pandas 'transform'?

Suppose you have the following SQL table: A B C 2 1 4 3 4 5 3 1 1 1 4 0 5 0 1 And you want to add/show a column containing the me

Solution 1:

You can join to a derived table that contains the aggregate value for each grouping of b

select * from mytable t1
join (
    select avg(a), b
    from mytable
    group by b
) t2 on t2.b = t1.b

or using a subquery

select *, (select avg(a) from mytable t2 where t2.b = t1.b)
from mytable t1

the question is tagged both mysql and psql, so I'm not sure which db you're using. But on postgres you can use window functions

select *, avg(a) over (partition by b) 
from mytable