SQLTeam.com | Weblogs | Forums

MIN aggregate function to a BIT field


#1

If i use Aggregate Function MIN() on BIT data type column then i got below error message.
"Operand data type bit is invalid for min operator."

I am clear about how to avoid the error, but i would like to know
why bit data type columns are not allowed in MIN Aggregate function? Is there any reason behind that?


#2

Since bit is either 0 or 1 why the MIN? The MIN is 0.


#3

not if all rows contain a 1 in the bit column!

However, why do you want to MIN a bit column anyway? What are you trying to achieve?


#4

don't forget that bit column may be NULL.


#5

@gbritton, yep, I thought of that after posting
@khtan, if the MIN worked the NULL would not be considered, but it is good information as @mmkrishna1919 did not indicate what they were trying for


#6

To me it's a silly restriction. SQL should allow MIN/MAX/SUM on bits also.


#7

Not sure about that. They're not (typically) used to count things. More useful might be AND() OR() XOR() aggregates, but that's just dreamland.

You can simulate AND() like this

1 = ALL(Select bit_col from table)

and OR() like this:

1 = ANY(Select bit_col from table)

XOR() is a bit harder.


#8

Thanks all for your replies.
@@djj55: I was going through MSDN library, i came across this statement. But i didn't find any technical reason behind this restriction.


#9

There is a technical reason, what is the sum of 1 + 1? Well, 2, obviously! However, 2 cannot fit in a bit! The sum would take on the type of the column being summed, unless you write

sum(cast mybit as int)

Also a theoretical reason; Since bits are used to represent boolean values, it makes no sense to do normal arithmetic on them. e.g. what is True plus False?


#10

True, but that wouldn't necessarily prevent MIN/MAX. Still a needless restriction in the name of pedantry; I even suspect at some point it might be lifted and you'll be able to use MIN/MAX at least.


#11

You must have a more suspicious nature than I do :smile:


#12

Probably :smile:. I prefer practicality to win out over pedantry, even if/when it doesn't happen.


#13

An integer data type that can take a value of 1, 0, or NULL.

I hate to be completely pedantic but...

(Have you noticed that when people say "I hate to..." they really mean "I'm about to...")

From BOL: Bit Data-type "An integer data type that can take a value of 1, 0, or NULL." If it's an integer it should share the same set of operations allowable to other integer. Yes, SUM would have a ridiculously low overflow value but MIN and MAX would definitely have useful applications.

Now, if you'll excuse me, I have to go back to making sure that all of my papers are at true right angles on my desk....


#14

select sum(cast(MyBit as integer)) & 1 as XOR from MyTable


#15

Shows the problem nicely. BOL says its an integer. SQL does not treat a bit like an integer. You have to cast it (as you did) or get

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for max operator.

#16

Well... What is minimum of a TRUE or FALSE value?
It just happens that Microsoft has chosen 1 for TRUE och 0 for FALSE.

Use MIN(CASE WHEN BitColumn = 1 THEN 1 ELSE 0 END)
Or MAX(CASE WHEN BitColumn = 1 THEN 1 ELSE 0 END)


#17

It seems to me that MIN would cause a scan, anyway. Wouldn't it be simpler and faster to use EXISTS in this case?

     IF EXISTS (SELECT * FROM dbo.YourTable WHERE SomeBitColumn = 0)
        SELECT 0
   ELSE SELECT 1
;

#18

This does assume no nulls...


#19

Heh... I think that anyone that would allow NULLs in a BIT datatype has bigger problems.


#20

I think you could have situations where a bit value is unknown, and you're unsure yet whether the bit should be on on or off.