How to calculate the average value of a field expression in result set groups using the AVG() function?

AVG(expr) is a MySQL built-in aggregate function that calculates the average value of a field expression in result set groups. For example:

SELECT help_category_id, AVG(help_topic_id), COUNT(help_topic_id)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+--------------------+----------------------+
  -- | help_category_id | AVG(help_topic_id) | COUNT(help_topic_id) |
  -- +------------------+--------------------+----------------------+
  -- |                1 |             0.5000 |                    2 |
  -- |                2 |            21.9143 |                   35 |
  -- |                3 |           635.5932 |                   59 |
  -- |                4 |             4.5000 |                    2 |
  -- |                5 |            42.3333 |                    3 |
  -- ...
  -- +------------------+--------------------+----------------------+

SELECT help_category_id, help_topic_id
  FROM mysql.help_topic WHERE help_category_id = 5;
  -- +------------------+---------------+
  -- | help_category_id | help_topic_id |
  -- +------------------+---------------+
  -- |                5 |            40 |
  -- |                5 |            43 |
  -- |                5 |            44 |
  -- +------------------+---------------+

AVG() is also a window function, you can call it with the OVER clause to calculate the average value of the given expression in the current window. For example:

SELECT help_topic_id, help_category_id, 
  AVG(help_topic_id) OVER w
  FROM mysql.help_topic 
  WINDOW w AS (PARTITION BY help_category_id);
  -- +---------------+------------------+---------------------------+
  -- | help_topic_id | help_category_id | AVG(help_topic_id) OVER w |
  -- +---------------+------------------+---------------------------+
  -- |             0 |                1 |                    0.5000 |
  -- |             1 |                1 |                    0.5000 |
  -- |             2 |                2 |                   21.9143 |
  -- |             6 |                2 |                   21.9143 |
  -- |             7 |                2 |                   21.9143 |
  -- ...
  -- +---------------+------------------+---------------------------+

Reference information of the AVG() function:

AVG(DISTINCT expr): avg
  Returns the average value of expr. The DISTINCT option can be used
  to return the average of the distinct values of expr.

Arguments, return value and availability:
  expr: Required. The field expression in result set groups.
  DISTINCT: Optional. If provided, only distinct values of expr are used.
  avg: Return value. The average value of the input expression.
  Available since MySQL 4.

Related MySQL functions:

• MIN() - Minimum Value in Group
• MAX() - Maximum Value in Group

⇒ BIT_AND() - Bitwise AND in Group

⇐ ANY_VALUE() - Any Value in Group

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References