Averaging an average in mySQL

Question

I have a table

car

 id |  person_id  |  mpg
------------------------  
 4  |     1       |  50
 5  |     1       |  15
 6  |     2       |  10
 7  |     2       |  28
 8  |     3       |  33

I need to get an average of each person's mpg and then an average for the group.

person 1 avg = (50 + 15) / 2 = 32.5
person 2 avg = (10 + 28) / 2 = 19
person 3 avg = 33

group average = 32.5 + 19 + 33 / 3 = 28.1

Is there a query that will do what I need?

Answer 1

you cannot solve this in 1 query, but you have to use 2 queries or 1 query en solve the overal average in your code

select person, avg(mpg) from cat group by person

Answer 2

SELECT person_id, AVG(mpg) from car group by person_id;

If you want to get an average for the group, you should probably do this:

SELECT AVG(mpg) from car;

Unless you really want to average the averages, which seems a bit dubious to me:

SELECT AVG(average) from (SELECT person_id, AVG(mpg) as average from car group by person_id);

Answer 3

SELECT person_id, AVG(mpg) AS mpg_avg FROM car GROUP BY person_id WITH ROLLUP

The WITH ROLLUP-modifier will add a line to the result set where persion_id is NULL and mpg_avg is the average over the whole result set (MySQL >= 4.1.1):

 person_id | mpg 
------------------
         1 | 32.5 
         2 | 19.0
         3 | 33.0
      NULL | 27.2