BIT_COUNT() function in MySQL is used to return the number of bits that are active in the given input. Active bits can be counted as a number of 1 presented in a binary number.
Syntax :
BIT_COUNT(number)
Parameter : This method accepts only one parameter.
-
number –
Input integer whose number of active bits we want to calculate.
Returns : It returns the number of active bits set in the number.
Example-1 :
Finding the number of active bits for number 0 using BIT_COUNT Function. As the equivalent binary number of the given input is 0 so, the number of 1 in an equivalent binary number is also 0. So, here we will get 0 active bits.
SELECT BIT_COUNT(0) AS ActiveBits;
Output :
ACTIVEBITS |
---|
0 |
Example-2 :
Finding the number of active bits for number 14 using BIT_COUNT Function. We know The equivalent binary representation of 14 is 1110. Here we can see the number of 1 present is 3. So, the result will be 3.
SELECT BIT_COUNT(14) AS ActiveBits;
Output :
ACTIVEBITS |
---|
3 |
Example-3 :
Finding the number of active bits for the following binary number using BIT_COUNT Function. As the number of 1 in the following example are 0, 1, 4 and 7 respectively, so we will get 0, 1, 4 and 7 active bits in result.
SELECT BIT_COUNT(b'0000') AS ActiveBits1, BIT_COUNT(b'00100') AS ActiveBits2, BIT_COUNT(b'01010101') AS ActiveBits3, BIT_COUNT(b'1111111') AS ActiveBits4;
Output :
ACTIVEBITS1 | ACTIVEBITS2 | ACTIVEBITS3 | ACTIVEBITS4 |
---|---|---|---|
0 | 1 | 4 | 7 |
Example-4 :
BIT_COUNT Function can also be used on column data. To demonstrate create a table named HolidayDetails.
CREATE TABLE HolidayDetails ( Holiday_id INT AUTO_INCREMENT, YearDetails YEAR(4), MonthDetails INT(2) UNSIGNED ZEROFILL, DayDetails INT(2) UNSIGNED ZEROFILL, PRIMARY KEY(Holiday_id));
Inserting some data to the HolidayDetails table –
INSERT INTO HolidayDetails (YearDetails, MonthDetails, DayDetails) VALUES (2021, 1, 1), (2021, 1, 14), (2021, 1, 26), (2021, 2, 19), (2021, 2, 21), (2021, 3, 10);
So, the HolidayDetails Table is as follows –
SELECT * from HolidayDetails;
HOLIDAY_ID | YEARDETAILS | MONTHDETAILS | DAYDETAILS |
---|---|---|---|
1 | 2021 | 1 | 1 |
2 | 2021 | 1 | 14 |
3 | 2021 | 1 | 26 |
4 | 2021 | 2 | 19 |
5 | 2021 | 2 | 21 |
6 | 2021 | 3 | 10 |
Now we are going to find the number of holidays per month –
SELECT YearDetails, MonthDetails, BIT_COUNT(BIT_OR(1<<DayDetails)) AS No_Of_Holidays FROM HolidayDetails GROUP By YearDetails, MonthDetails;
Output :
YEARDETAILS | MONTHDETAILS | NO_OF_HOLIDAYS |
---|---|---|
2021 | 01 | 3 |
2021 | 02 | 2 |
2021 | 03 | 2 |