MySQL BIT_COUNT() function

BIT_COUNT() function

MySQL BIT_COUNT() returns the number of bits set in the argument.

Syntax:

BIT_COUNT(N)

MySQL Version: 8.0

Where N is an integer.

Example: MySQL BIT_COUNT() function

The following MySQL statement will return the number of bits for the specified numbers in the argument.

Code:

SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255);

Output:

mysql> SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255);
+--------------+---------------+---------------+----------------+
| BIT_COUNT(8) | BIT_COUNT(24) | BIT_COUNT(28) | BIT_COUNT(255) |
+--------------+---------------+---------------+----------------+
|            1 |             2 |             3 |              8 | 
+--------------+---------------+---------------+----------------+
1 row in set (0.01 sec)

Example - 2

The following MySQL statement will return the number of bits for arguments specified.

Code:

SELECT BIT_COUNT(b'1000'),BIT_COUNT(b'11000'),BIT_COUNT(b'11111111');

Output:

mysql> SELECT BIT_COUNT(b'1000'),BIT_COUNT(b'11000'),BIT_COUNT(b'11111111');
+--------------------+---------------------+------------------------+
| BIT_COUNT(b'1000') | BIT_COUNT(b'11000') | BIT_COUNT(b'11111111') |
+--------------------+---------------------+------------------------+
|                  1 |                   2 |                      8 | 
+--------------------+---------------------+------------------------+
1 row in set (0.00 sec)

Calculate the number of days per month a user has visited a web page

In the following example we have used bit group functions and operators to calculate the number of days per month a user has visited a web page. The example table contains year-month-day values representing visits by users to the page. Here is the table structure with some raw data:

mysql> CREATE TABLE table1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
Query OK, 0 rows affected (0.48 sec)
mysql> INSERT table1 VALUES(2012, 1, 1), (2012, 1, 20), (2012, 1, 30), (2012, 2, 2), (2012, 2, 24), (2012, 2, 24);
Query OK, 6 rows affected (0.22 sec)
mysql> SELECT * FROM table1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2012 |    01 |   01 |
| 2012 |    01 |   20 |
| 2012 |    01 |   30 |
| 2012 |    02 |   02 |
| 2012 |    02 |   24 |
| 2012 |    02 |   24 |
+------+-------+------+
6 rows in set (0.00 sec)

Use the following query to determine how many different days in each month these visits occur. The query calculates how many different days appear in the table for each year-month combination, with automatic removal of duplicate entries.

mysql> SELECT year, month, BIT_COUNT(BIT_OR(1<<day)) AS days FROM table1 GROUP BY year, month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2012 |    01 |    3 |
| 2012 |    02 |    2 |
+------+-------+------+
2 rows in set (0.03 sec)

Previous: UNCOMPRESSED_LENGTH()
Next: BITWISE AND