SQL - Conditional average

Admin 1
Viewed 44

So i have a query like so in MySQL;

  select Schedule.trackName, Ratings.rating, count(trackName)
from Ratings 
inner Join Schedule on Ratings.trackNo = Schedule.trackNo 
Where rating > 2
group by 1
having count(trackName) > 1;

If i wanted to take the average of the ratings output from this query, how would i implement that

aggregate-functions mysql average sql
1 Answers

You can use the aggregate AVG function to achieve that:

Returns the average value of expr

Here is your code with AVG in to return the average and count, grouped by the trackName column:

SELECT  Schedule.trackName,
        AVG(Ratings.rating),
        COUNT(trackName)
  FROM  Ratings 
    INNER JOIN Schedule ON Ratings.trackNo = Schedule.trackNo 
  WHERE rating > 2
  GROUP BY Schedule.trackName
  HAVING COUNT(trackName) > 1;

Incidentally, it's better to use the column names in GROUP BY clauses rather than numbers. That little trick doesn't work on other RDBMS.

More information about AVG can be seen in the official docs.

user_21697620
Related