MySQL BIT_COUNT() function

Last update on April 04 2022 13:11:14 (UTC/GMT +8 hours)

BIT_COUNT() function

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

Syntax:

BIT_COUNT(N)

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);

Sample 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)

PHP script:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="https://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>php mysql examples</title>
</head>
<body>
<?php
echo "<h2>PHP MySQL BIT_COUNT() example : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='100' align='center'>BIT_COUNT(8)</td><td width='100' align='center'>BIT_COUNT(24)</td>
<td width='100' align='center'>BIT_COUNT(28)</td><td width='100' align='center'>BIT_COUNT(255)</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255)");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(8)"] . "</td>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(24)"] . "</td>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(28)"] . "</td>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(255)"] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>

View the example in browser

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');

Sample 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