CODEWITHSUNDEEP
mysqlsql
jda
jda

Reputation: 536

Update row value in MySQL by averaging values from a subset of other rows

I'm a beginner with SQL and am trying to write a query that's currently beyond my abilities. This is perhaps complicated by the fact that the table I'm working with has, for reasons I can't control or change, no primary key. It's a MySQL database. Here is a simplified version of the table, with only four rows:

pid year    name            team    wOBAplus    PA
147 2002    Barry Bonds     MTL     0.466       276
147 2002    Barry Bonds     OAK     0.495       318
147 2002    Barry Bonds             NULL        594
147 2003    Barry Bonds     OAK     0.456       522
212 2014    Mike Trout      SEA     0.391       567

The wOBAplus column has values for all rows where the team column isn't empty. What I'd like to do is add values in the remaining rows, using the following formula:

wOBAplus_noteam = (sum(wOBAplus_team * PA_team))/PA_noteam

So, in this case, I would want the wOBAplus that's currently NULL updated to (0.466*276 + 0.495*318)/594. I would want all other rows ignored.

Is this something that can be achieved using the table as currently structured? If not, what are my alternatives?

Thanks in advance for any help you can provide.

Upvotes: 0

Views: 111

Answers (1)

jpw
jpw

Reputation: 44911

I think this is what you want:

UPDATE Table1
JOIN (
  SELECT pid, year, SUM(wOBAplus*PA) AS woa 
  FROM Table1 
  GROUP BY pid, year
 ) a ON a.pid = Table1.pid AND a.year = Table1.year
SET wOBAplus =  woa/pa
WHERE wOBAplus IS NULL

Sample SQL Fiddle

Upvotes: 1

Related Questions