Question

页: 1

汽车

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

我需要获得每个人的体格,然后得到该群体的平均数。

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

是否有人问我需要做什么?

Answer 1

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);

Answer 2

在1个询问中,你无法解决这一问题,但你不得不使用2个问询或1个问询,解决你法典中的超平均数。

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

Answer 3

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

<代码>WITH ROLUP-modifier将添加一个行文,将persion_id改为NUL和mpg_avg为整个成果集(MySQL >= 4.1.1):



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

友情链接