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?
Since bit is either 0 or 1 why the MIN? The MIN is 0.
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?
don't forget that bit column may be NULL.
@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
To me it's a silly restriction. SQL should allow MIN/MAX/SUM on bits also.
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.
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.
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?
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.
You must have a more suspicious nature than I do 
Probably . I prefer practicality to win out over pedantry, even if/when it doesn't happen.
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....
select
sum(cast(MyBit as integer)) & 1 as XOR
from
MyTable
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.
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)
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
;This does assume no nulls...
Heh... I think that anyone that would allow NULLs in a BIT datatype has bigger problems.
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.