How to count items of a field expression in result set groups using the COUNT() function?

COUNT(expr) is a MySQL built-in aggregate function that counts items of a field expression in result set groups. For example:

SELECT help_category_id, COUNT(help_topic_id), COUNT(*)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+----------------------+----------+
  -- | help_category_id | COUNT(help_topic_id) | COUNT(*) |
  -- +------------------+----------------------+----------+
  -- |                1 |                    2 |        2 |
  -- |                2 |                   35 |       35 |
  -- |                3 |                   59 |       59 |
  -- |                4 |                    2 |        2 |
  -- |                5 |                    3 |        3 |
  -- |                6 |                    1 |        1 |
  -- ...
  -- +------------------+----------------------+----------+

SELECT help_category_id, COUNT(DISTINCT help_topic_id), COUNT(*)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+-------------------------------+----------+
  -- | help_category_id | COUNT(DISTINCT help_topic_id) | COUNT(*) |
  -- +------------------+-------------------------------+----------+
  -- |                1 |                             2 |        2 |
  -- |                2 |                            35 |       35 |
  -- |                3 |                            59 |       59 |
  -- |                4 |                             2 |        2 |
  -- |                5 |                             3 |        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 |
  -- +------------------+---------------+

If you want to count items with a certain condition, you use the IF() function to discard items as NULL values. For example:

SELECT help_category_id, COUNT(help_topic_id) AS Any_IDs,
  COUNT(IF(help_topic_id%2=1, 1, NULL)) AS Odd_IDs
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+---------+---------+
  -- | help_category_id | Any_IDs | Odd_IDs |
  -- +------------------+---------+---------+
  -- |                1 |       2 |       1 |
  -- |                2 |      35 |      17 |
  -- |                3 |      59 |      30 |
  -- |                4 |       2 |       1 |
  -- |                5 |       3 |       1 |
  -- ...
  -- +------------------+---------+---------+

COUNT() is also a window function, you can call it with the OVER clause to count non-NULL rows of the given expression in the current window. For example:

SELECT help_topic_id, help_category_id, 
  COUNT(help_topic_id) OVER w
  FROM mysql.help_topic 
  WINDOW w AS (PARTITION BY help_category_id);
  -- +---------------+------------------+-----------------------------+
  -- | help_topic_id | help_category_id | COUNT(help_topic_id) OVER w |
  -- +---------------+------------------+-----------------------------+
  -- |             0 |                1 |                           2 |
  -- |             1 |                1 |                           2 |
  -- |             2 |                2 |                          35 |
  -- |             6 |                2 |                          35 |
  -- |             7 |                2 |                          35 |
  -- |             8 |                2 |                          35 |
  -- |             9 |                2 |                          35 |
  -- ...
  -- +---------------+------------------+-----------------------------+

There seems to be an issue when using COUNT() as a window function over the sorted default window as shown below. The CountOfW2 column should be 682 for all rows.

SELECT help_topic_id AS tic, help_category_id AS cid,
  COUNT(*) OVER w1 AS CountOfW1,
  ROW_NUMBER() OVER w1 AS RowInW1,
  COUNT(*) OVER w2 AS CountOfW2,
  ROW_NUMBER() OVER w2 AS RowInW2
  FROM mysql.help_topic 
  WINDOW
    w1 AS (PARTITION BY help_category_id),
    w2 AS (ORDER BY help_category_id);
  -- +-----+-----+-----------+---------+-----------+---------+
  -- | tic | cid | CountOfW1 | RowInW1 | CountOfW2 | RowInW2 |
  -- +-----+-----+-----------+---------+-----------+---------+
  -- |   0 |   1 |         2 |       1 |         2 |       1 |
  -- |   1 |   1 |         2 |       2 |         2 |       2 |
  -- |   2 |   2 |        35 |       1 |        37 |       3 |
  -- |   6 |   2 |        35 |       2 |        37 |       4 |
  -- |   7 |   2 |        35 |       3 |        37 |       5 |
  -- |   8 |   2 |        35 |       4 |        37 |       6 |
  -- |   9 |   2 |        35 |       5 |        37 |       7 |
  -- |  10 |   2 |        35 |       6 |        37 |       8 |
  -- ...
  -- | 677 |  42 |         5 |       2 |       681 |     678 |
  -- | 678 |  42 |         5 |       3 |       681 |     679 |
  -- | 679 |  42 |         5 |       4 |       681 |     680 |
  -- | 680 |  42 |         5 |       5 |       681 |     681 |
  -- | 681 |  43 |         1 |       1 |       682 |     682 |
  -- +-----+-----+-----------+---------+-----------+---------+

Reference information of the COUNT() function:

COUNT(DISTINCT expr): int
  Returns a count of the number of non-NULL values of expr in the rows
  retrieved by a SELECT statement.

  If argument is '*', it counts all rows in the group.

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.
  int: Return value. The count of non-NULL values of the input expression.
  Available since MySQL 4.

⇒ GROUP_CONCAT() - Concatenating Items in Group

⇐ BIT_XOR() - Bitwise XOR in Group

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References