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, 256🔥, 0💬
Popular Posts:
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL? If you enter millise...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...