How can I get a count of a bit-type column?

Question

I'm trying to use SEDE to determine how many tag-based badges have been awarded in total. My typical strategy is to sum:

select sum(TagBased)
from Badges

Unfortunately, since TagBased is a bit value, I get this error:

Operand data type bit is invalid for sum operator.

Out of desperation, I tried count:

select count(TagBased)
from Badges

This counts the number of non-null values, which in this case is identical to count(*). So how can a bit value be used in aggregate?

score 15 · Answer 1 · edited Aug 29 '17 at 15:31

If you just want to look at tag-based badges and don't care about the rest, you can move the test to the where clause:

select count(*)
from Badges 
where TagBased = 1

The canonical solution would be to cast the bit value to a data type that can be summed:

select sum(cast(TagBased as decimal))
from Badges

An even more flexible solution is to do the conversion yourself with a CASE statement:

select sum(case TagBased when 1 then 100.0 end)/count(*) tag_based_per
from Badges

Alternatively, in newer versions of SQL Server there's an IIF function:

select sum(iif(TagBased = 1, 1, 0))
from Badges

Finally, if you are golfing, you can trick SQL into converting the bit for you:

select sum(TagBased+0)
from Badges

The credit for this technique goes to Kenneth Baltrinic's answer to a more specific question.

For reference (and potential forking): my test queries.

maybe you can add this one too `select sum(TagBased&1) from Badges` ? — Saidolim, Dec 18 '15 at 15:26
I had never been to http://data.stackexchange.com until I saw your last link – now I feel the rest of my evening will evaporate… — TwoStraws, Dec 20 '15 at 22:49

score 4 · Answer 2 · edited Jan 18 '21 at 12:30

Another method to COUNT only 1 from BIT column is to use NULLIF:

SELECT COUNT(NULLIF(TagBased,0)) AS result
FROM Badges b;

One more method using [SIGN](https://msdn.microsoft.com/en-us/library/ms188420.aspx):

SELECT SUM(SIGN(TagBased)) AS result
FROM Badges b;

And one more way. I am not sure why I've written it:

SELECT DISTINCT TOP 1 RANK() OVER(ORDER BY TagBased DESC) - 1
FROM Badges b
ORDER BY 1 DESC;

score 1 · Answer 3 · answered Dec 18 '15 at 18:56

One of the ways using bit operators.

select sum(TagBased&1) from Badges

Generally, you can store several bit values in one integer. For example I've 5 types of bages. You can put values like :

if you need to set Code and byte at the same time. It will be 1 + 4 = 5

Now how to count Code bages count

select sum(TagBased & 1) from Badges

Now how to count Byte bages count

select sum(TagBased & 4)/4 from Badges

Now at the same time, Code and Byte bages count at all

select count(TagBased & 5) from Badges where TagBased & 5 > 0

3 Answers3