Collections:
AVG() - Average Value in Group
How to calculate the average value of a field expression in result set groups using the AVG() function?
✍: FYIcenter.com
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:
⇒ BIT_AND() - Bitwise AND in Group
⇐ ANY_VALUE() - Any Value in Group
2023-11-18, 259🔥, 0💬
Popular Posts:
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...