Applying the MIN aggregate function to a BIT field

I want to write the following query:

SELECT   ..., MIN(SomeBitField), ...
FROM     ...
WHERE    ...
GROUP BY ...

The problem is, SQL Server does not like it, when I want to calculate the minimum value of a bit field it returns the error Operand data type bit is invalid for min operator.

I could use the following workaround:

SELECT   ..., CAST(MIN(CAST(SomeBitField AS INT)) AS BIT), ...
FROM     ...
WHERE    ...
GROUP BY ...

But, is there something more elegant? (For example, there might be an aggregate function, that I don't know, and that evaluates the logical and of the bit values in a field.)

One option is MIN(SomeBitField+0). It reads well, with less noise (which I would qualify as elegance).

That said, it's more hack-ish than the CASE option. And I don't know anything about speed/efficiency.

Since there are only two options for BIT, just use a case statement:

SELECT CASE WHEN EXISTS (SELECT 1 FROM ....) THEN 1 ELSE 0 END AS 'MinBit'
FROM ...
WHERE ...

This has the advantage of:

• Not forcing a table scan (indexes on BIT fields pretty much never get used)
• Short circuiting TWICE (once for EXISTS and again for the CASE)

It is a little more code to write but it shouldn't be terrible. If you have multiple values to check you could always encapsulate your larger result set (with all the JOIN and FILTER criteria) in a CTE at the beginning of the query, then reference that in the CASE statements.

This query is the best solution:

SELECT CASE WHEN MIN(BitField+0) = 1 THEN 'True' ELSE 'False' END AS MyColumn
 FROM MyTable

When you add the BitField+0 it would automatically becomes like int

select min(convert(int, somebitfield))

or if you want to keep result as bit

select convert(bit, min(convert(int, somebitfield)))

Try the following Note: Min represent And aggregate function , Max represent Or aggregate function

SELECT   ..., MIN(case when SomeBitField=1 then 1 else 0 end), MIN(SomeBitField+0)...
FROM     ...
WHERE    ...
GROUP BY ...

same result

This small piece of code has always worked with me like a charm:

CONVERT(BIT, MIN(CONVERT(INT, BitField))) as BitField

AVG(CAST(boolean_column AS FLOAT)) OVER(...) AS BOOLEAN_AGGREGATE

Give a fuzzy boolean :

• 1 indicate that's all True;

• 0 indicate that's all false;

• a value between ]0..1[ indicate partial matching and can be some percentage of truth.