I have a set of data which defines ratings in a survey table for events at different locations.
I am trying to display the average rating for each location with the average for all locations in the final row.
At the moment, my query looks like this:
SELECT
IFNULL(l.title, 'Total') AS location,
ROUND(AVG(NULLIF(s.rating, 0)), 2) AS rating
FROM surveys s
INNER JOIN events e
ON e.id = s.event_id
INNER JOIN locations l
ON l.id = e.location_id
GROUP BY l.title WITH ROLLUP
This gives me the following values for each location:
8.22, 8.67, 8.67, 8.56, 7.86, 8.50. 8.78, 7.33
However, the final Total is returned as 8.37 but it should actually be 8.32.
Am I doing something wrong?
The rollup uses the COUNT and SUM of the entire table, then does the division. It does not use the incremental results and average them.
You are rounding the incremental results to two decimal places, but their value is not actually that. If you output the true value you would see that average makes more sense.
Try also outputing the SUM and COUNT so you can verify things for yourself.