Applying the MIN aggregate function to a BIT field

Question

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

@Adam Robinson: Obviously, `Operand data type bit is invalid for min operator.` — Andomar, Aug 09 '11 at 14:29
Don't know about more elegant but it is a *bit* shorter. `cast(min(SomeBitField+0) as bit)` — Mikael Eriksson, Aug 09 '11 at 14:46
@Andomar: Obviously if you already know the problem, yes, but others with similar issues may search on the *error message*, so information like that needs to be in the question. — Adam Robinson, Aug 09 '11 at 14:47
This is just stupid. There is no reason why data type bit should be invalid for min/max, since *obviously* 0 < 1 and thus bit values are orderable. — Michael, May 07 '21 at 16:52
Also, I *always* run into this sort of issue when I'm *forced* to aggregate a field that I *know* (due to constraints) will always be the same value, so why can't there just be an aggregate function FIRST() or something that just returns the first or *any* instance of the field? — Michael, Oct 19 '21 at 17:23

score 186 · Answer 1 · edited Apr 12 '23 at 18:21

186

One option is CAST(MIN(SomeBitField+0) AS BIT). 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.

edited Apr 12 '23 at 18:21

Hamid Heydarian

802
9
16

answered May 09 '12 at 19:25

Ben Mosher

13,251
7
69
80

@Ben, Thanks a lot, This alson help me for my SQL Query. – PatsonLeaner Jun 01 '18 at 11:55
1

@Ben, do you have some documentation of **+0** ?, tried to look for it but not find anything, just for references :) – Francisco Sevilla Jan 28 '20 at 22:31
2

@FranciscoSevilla I believe it's because of the implicit precedence conversion: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15 – Ben Mosher Jan 29 '20 at 19:43
2

Presumably is must be cast back to BIT again? `CAST(MIN(SomeBitField+0) AS BIT)` – Reversed Engineer Oct 27 '21 at 13:19

score 36 · Accepted Answer · answered Aug 09 '11 at 14:59

36

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.

answered Aug 09 '11 at 14:59

JNK

63,321
15
122
138

10

There are three options for `bit` if it is nullable. – Martin Smith Aug 10 '11 at 23:31
@martin - Good point. It wouldn't change the query in this circumstance though unless they want to handle `NULL` in a specific way, since we are testing equality – JNK Aug 10 '11 at 23:35
1

If the `bit` column consisted entirely of `NULL` values then `MIN` should return `NULL`. – Martin Smith Aug 10 '11 at 23:37
9

I must be a "lesser" programmer, but I would have like to have seen a full example of the `select 1 from ...` subquery. It does not quite make sense. – Vaccano Mar 30 '12 at 16:17
2

@Vaccano - `SELECT 1 FROM Outertable WHERE bitfield=1` – JNK Mar 30 '12 at 17:02
2

The original question included a GROUP BY though. You need to include the group by criteria in the WHERE. – Tmdean Jun 04 '15 at 17:07
This requires rewriting almost the entire query within the column expression, doesn’t it? If the query is expected to have filtered the data to less than a few hundreds of rows, the `CAST(MIN(CAST(«expression» AS INT)) AS BIT)` method is a lot easier to think about and maintain. – binki Aug 31 '19 at 18:12

score 9 · Answer 3 · answered Apr 15 '13 at 14:31

9

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

answered Apr 15 '13 at 14:31

Israel Margulies

8,656
2
30
26

score 7 · Answer 4 · answered Jul 07 '16 at 21:46

7

select min(convert(int, somebitfield))

or if you want to keep result as bit

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

answered Jul 07 '16 at 21:46

Vernard Sloggett

336
4
10

score 7 · Answer 5 · answered Mar 22 '18 at 06:34

7

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

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

answered Mar 22 '18 at 06:34

Chaos Legion

2,730
1
15
14

Waleed A.K. · Answer 6 · 2013-11-25T19:03:19.463

6

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

edited Nov 25 '13 at 19:03

answered Nov 25 '13 at 18:26

Waleed A.K.

1,596
13
13

score 2 · Answer 7 · answered Jan 06 '17 at 10:58

2

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.

answered Jan 06 '17 at 10:58

user7370003

47
3

Applying the MIN aggregate function to a BIT field

7 Answers7

Linked