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 theCASE
)
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.
精彩评论