How to calculate the Bitwise AND value of a field expression in result set groups using the BIT_AND() function?

BIT_AND(expr) is a MySQL built-in aggregate function that calculates the Bitwise AND value of a field expression in result set groups. For example:

SELECT help_category_id, BIN(BIT_AND(help_topic_id)), COUNT(help_topic_id)
  FROM mysql.help_topic GROUP BY help_category_id;
  -- +------------------+-----------------------------+----------------------+
  -- | help_category_id | BIN(BIT_AND(help_topic_id)) | COUNT(help_topic_id) |
  -- +------------------+-----------------------------+----------------------+
  -- |                1 | 0                           |                    2 |
  -- |                2 | 0                           |                   35 |
  -- |                3 | 0                           |                   59 |
  -- |                4 | 100                         |                    2 |
  -- |                5 | 101000                      |                    3 |
  -- ...
  -- +------------------+-----------------------------+----------------------+

SELECT help_category_id, BIN(help_topic_id)
  FROM mysql.help_topic WHERE help_category_id = 5;
  -- +------------------+--------------------+
  -- | help_category_id | BIN(help_topic_id) |
  -- +------------------+--------------------+
  -- |                5 | 101000             |
  -- |                5 | 101011             |
  -- |                5 | 101100             |
  -- +------------------+--------------------+

BIT_AND() is also a window function, you can call it with the OVER clause to calculate the bitwise AND value of the given expression in the current window. For example:

SELECT help_topic_id, help_category_id, 
  BIN(BIT_AND(help_topic_id) OVER w)
  FROM mysql.help_topic 
  WINDOW w AS (PARTITION BY help_category_id);
  -- +---------------+------------------+------------------------------------+
  -- | help_topic_id | help_category_id | BIN(BIT_AND(help_topic_id) OVER w) |
  -- +---------------+------------------+------------------------------------+
  -- |             0 |                1 | 0                                  |
  -- ...
  -- |             4 |                4 | 100                                |
  -- |             5 |                4 | 100                                |
  -- |            40 |                5 | 101000                             |
  -- |            43 |                5 | 101000                             |
  -- |            44 |                5 | 101000                             |
  -- |            41 |                6 | 101001                             |
  -- |            42 |                7 | 101010                             |
  -- |            45 |                9 | 0                                  |
  -- ...
  -- +---------------+------------------+------------------------------------+

Reference information of the BIT_AND() function:

BIT_AND(expr): bin
  Returns the bitwise AND of all bits in expr.

  If there are no matching rows, BIT_AND() returns a neutral value
  (all bits set to 1).

Arguments, return value and availability:
  expr: Required. The field expression in result set groups.
  bin: Return value. The bitwise AND value of the input expression.
  Available since MySQL 5.7.

⇒ BIT_OR() - Bitwise OR in Group

⇐ AVG() - Average Value in Group

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References