SQL Perform an Action on Every Row that Meets Condition

Here is the sqlfiddle

I am currently trying to average rent over a particular zip code wildcard. For example, if the zipcode for 20000 has rent value 0, it will instead insert the average rent for (non-zero) zipcodes matching the '20%' wildcard. Currently, however, it is averaging all of the rents, regardless of the wildcard. Here is what I am getting:

rent    zip
2375    20000
1000    20001
2000    20002
5000    30000
1500    20003
2375    30001

but this is what I want:

rent    zip
1500    20000
1000    20001
2000    20002
5000    30000
1500    20003
5000    30001

Here is my current query:

SELECT IF(tmp.rent=0, 
          (select AVG(rent) from tmp where rent > 0 and zip LIKE CONCAT(substr(zip,1,2),'%')), 
          tmp.rent) as rent, 
       zip 
from tmp

1 answer

  • answered 2018-01-11 20:50 spencer7593

    won't this expression evaluate to TRUE for all non-null values of zip, in other words, "all of them" ?

     zip LIKE CONCAT(substr(zip,1,2),'%'))
    

    Seems like you want to qualify one of those the column references so it refers to a column from the outer query. It's good practice to qualify all column references.

    SELECT IF( t.rent=0
             , ( SELECT AVG(s.rent)
                   FROM tmp s
                  WHERE s.rent > 0
                    AND s.zip LIKE CONCAT(SUBSTR(t.zip,1,2),'%')
               )
             , t.rent
           ) AS rent
         , t.zip 
      FROM tmp t