Applying the MIN aggregate function to a BIT field

user_46571 0
Viewed 56468

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.)

tsql sql-server sql
7 Answers

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

user_3473740
Related