在MySQL中求平均值

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

如果你想得到这个组的平均值,你应该这样做:

SELECT AVG(mpg) from car;

除非你真的想取平均值,这对我来说有点可疑:

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

不能在1个查询中解决此问题,但必须使用2个查询或1个查询en解决代码中的总体平均值。

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

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

这个 WITH ROLLUP -修饰符将向结果集添加一行,其中 persion_id 是 NULL 和 mpg_avg 是整个结果集的平均值(mysql>=4.1.1):

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