Search code examples
mysqlsqlsubqueryaveragewindow-functions

Create a new column from existing columns

I want to create a new column from this example table but with one more condition that so far I couldn't figure out, I want to create an average holdings column that's specific to each city.

Name      | City   | Holdings 
Tom Jones | London | 42       
Rick James| Paris  | 83       
Mike Tim  | NY     | 83       
Milo James| London | 83

So in this example table London has more than one instance and accordingly it will have a unique value of '62.5' indicating an average of holdings that's specific to the value in the city column.

Name      | City   | Holdings | City Avg. Holdings 
Tom Jones | London | 42       | 62.5
Rick James| Paris  | 36       | 36
Mike Tim  | NY     | 70       | 70
Milo James| London | 83       | 62.5
Solution

  • In MySQL 8.0, this is straight-forward with window functions:

    select t.*, avg(holdings) over(partition by city) avg_city_holdings
from mytable t

    In earlier versions, you can join:

    select t.*, a.avg_city_holdings
from mytable t
left join (select city, avg(holdings) avg_city_holdings from mytable group by city) a
    on a.city = t.city