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, 408🔥, 0💬
Popular Posts:
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...