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 total the miles for each person and then average those totals.

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

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

Which is the same thing as totaling the number of miles and dividing by the number of people.

I cannot figure out a mySQL statement that will either give me the average across people or give me total the number of miles and the number of people so I can do the division.

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

These should work

Answer 2

As soon as the person_id and miles are indexed, the fastest method will be this:

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

This will allow using two separate indexes for miles and person_id, the first one being in no certain order, the second one using INDEX FOR GROUP BY.

The sum will be calculated in no certain order and requires only a single index scan, without table lookup.

The subquery will be executed once using INDEX FOR GROUP BY and cached.

The final division, hence, will be a single operation over a precalculated sum and precalculated COUNT.

Answer 3

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

Answer 4

Corrected: just as the other author:

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

友情链接