Reputation: 10088
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.
Upvotes: 3
Views: 206
Reputation: 36563
Corrected: just as the other author:
select sum(miles)/count(distinct person_id) avg_miles from tablename;
Upvotes: 0
Reputation: 425843
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
.
Upvotes: 2
Reputation: 265956
SELECT AVG(`miles_per_person`)
FROM
(SELECT SUM(`miles`) `miles_per_person`
FROM `persons`
GROUP BY `person_id`) `a`
Upvotes: 0
Reputation: 41877
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
Upvotes: 7