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?

Answer 1

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.

Answer 2

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

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

LiveDemo

---

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

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

LiveDemo2

---

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;

LiveDemo3