SUM() - Total Value in Group

Q

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

✍: FYIcenter.com

A

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

MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References

2023-12-01, 349🔥, 0💬