1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
Jehaiman Asmar
  • 15
  • 5

1 Answers1

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Does this work with multiple conditions where I could specify for instance matching the values of last names or holdings that exceed a certain number? – Jehaiman Asmar Sep 15 '20 at 22:33
  • @JehaimanAsmar: probably, yes, but it depends on the exact requirement (and on your MySQL version). You might want to [ask a new question](https://stackoverflow.com/questions/ask) for this. – GMB Sep 15 '20 at 22:40