开发者

avg in query - mysql

开发者 https://www.devze.com 2023-03-17 13:09 出处:网络
I have this query SELECT salary FROM worker W JOIN single_user U ON u.users_id_user = W.single_user_users_id_user

I have this query

  SELECT salary
    FROM worker W
    JOIN single_user U ON u.users_id_user = W.single_user_users_id_user
    JOIN university_开发者_开发问答has_single_user US ON US.single_user_users_id_user = U.users_id_user
    JOIN course C ON C.id_course = US.course_id_course
    JOIN formation_area FA ON FA.id_formation_area = C.formation_area_id_formation_area
   WHERE FA.area = "Multimédia"
GROUP BY users_id_user

...that gave this output:

salary
--------
1400.00
800.00

How can I calculate the avg of this output? If I add:

SELECT round(avg (salary), 0) 

...the output is again 1400.00 and 800.00, not the avg (because the group by).


Use:

 SELECT AVG(DISTINCT salary)
   FROM worker W
   JOIN single_user U ON u.users_id_user = W.single_user_users_id_user
   JOIN university_has_single_user US ON US.single_user_users_id_user = U.users_id_user
   JOIN course C ON C.id_course = US.course_id_course
   JOIN formation_area FA ON FA.id_formation_area = C.formation_area_id_formation_area
  WHERE FA.area = "Multimédia"

Because the salary column is not wrapped in an aggregate, per the documentation, the values you see are arbitrary (can't be guaranteed 100% of the time).

Usually, you'd need a derived table to get the average of the distinct values but MySQL's AVG supports using DISTINCT within it.

0

精彩评论

暂无评论...
验证码 换一张
取 消