[FIXED] Average and count with conditions - mysql

Issue

table name is data.

Columns - 'date', 'location, 'fp, 'TV'

Under date I will have multiple different dates but each date has a number of rows with the same date. Same with location.

I am trying to work out the average of TV for every time the date and location are the same and fp = 1, and insert the result into a new column called avgdiff

So I might have a number of rows with the date 2016-12-08 and location LA, with different numbers under fp and TV. So when the date is 2016-12-08 and location is LA, fp might equal 1, 4 times, and TV for those 4 rows might be 7.4, 8.2, 1, -2. So the avg will be 3.65.

I think I need to use avg and count functions with conditions but I am having a lot of trouble with this. I hope this makes sense.

Thanks

Solution

You can query for the average using a GROUP BY:

SELECT `date`, `location`, AVG(`TV`) AS `avgtv`
  FROM `data`
 WHERE `fp` = 1
 GROUP BY `date`, `location`

To update another table with your computed averages (which I strongly recommend against), you can use an UPDATE...JOIN with the above as a subquery:

UPDATE ratings r
  JOIN ( /* paste above query here */ ) t
    ON t.date = r.date AND t.location = r.location
   SET r.avgtv = t.avgtv

Answered By - shmosel