Total Averaging in mySQL

Question

My table looks like

person_id  | car_id | miles
------------------------------
    1      |   1    |  100
    1      |   2    |  200
    2      |   3    | 1000 
    2      |   4    |  500

I need to add miles for each person, and then average these amounts.

There are 2 people - person 1 drove 300 miles, person 2 drove 1,500 miles.

(300 + 1500) / 2 = 900 average number of miles per person.

This is the same as the total number of miles and the division by the number of people.

I cannot understand the mySQL statement, which will either give me the average number of people, or give me the total number of miles and the number of people so that I can perform the separation.

Answer 1

Total per person:

SELECT person_id, SUM(miles) FROM table GROUP BY person_id

Average

SELECT SUM(miles) / COUNT(DISTINCT person_id) FROM table

They have to work

Answer 2

Once the index person_idand mileswill be indexed, the fastest way would be:

SELECT  SUM(miles) /
        (
        SELECT  COUNT(*)
        FROM    (
                SELECT  DISTINCT person_id
                FROM    mytable
                ) q
        )
FROM    mytable

miles person_id, , - INDEX FOR GROUP BY.

, .

INDEX FOR GROUP BY .

, , COUNT.

Answer 3

SELECT AVG(`miles_per_person`)
FROM
   (SELECT SUM(`miles`) `miles_per_person`
      FROM `persons`
  GROUP BY `person_id`) `a`

Answer 4

Fixed: like another author:

select sum(miles)/count(distinct person_id) avg_miles from tablename;