Open In App

BIT_COUNT() function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 21 Jan, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads