Collections:
SUM() - Total Value in Group
How to calculate the total value of a field expression in result set groups using the SUM() function?
✍: FYIcenter.com
SUM(expr) is a MySQL built-in aggregate function that calculates the total value of a field expression in result set groups. For example:
SELECT help_category_id, SUM(help_topic_id), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+--------------------+----------------------+ -- | help_category_id | SUM(help_topic_id) | COUNT(help_topic_id) | -- +------------------+--------------------+----------------------+ -- | 1 | 1 | 2 | -- | 2 | 767 | 35 | -- | 3 | 37500 | 59 | -- | 4 | 9 | 2 | -- | 5 | 127 | 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 | -- +------------------+---------------+
SUM() is also a window function, you can call it with the OVER clause to calculate the total value of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, SUM(help_topic_id) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+---------------------------+ -- | help_topic_id | help_category_id | SUM(help_topic_id) OVER w | -- +---------------+------------------+---------------------------+ -- | 0 | 1 | 1 | -- | 1 | 1 | 1 | -- | 2 | 2 | 767 | -- | 6 | 2 | 767 | -- | 7 | 2 | 767 | -- | 8 | 2 | 767 | -- | 9 | 2 | 767 | -- ... -- +---------------+------------------+---------------------------+
Reference information of the SUM() function:
SUM(DISTINCT expr): sum Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr. If there are no matching rows, SUM() returns NULL. 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. sum: Return value. The total value of the input expression. Available since MySQL 4.
⇒ VAR_POP() - Population Standard Variance
⇐ STDDEV_SAMP() - Sample Standard Deviation
2023-12-01, 330🔥, 0💬
Popular Posts:
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...