The sum or average aggregate operation cannot take a bit data type as an argument.
Oh yes I fell for this one yesterday. It's not that I didn't know about it (in the back of my head) it's just that I forgot
I was answering one question in the microsoft forums and someone wanted to sum something, unfortunately the datatype was bit and as we all know bit data types can not be used with average or sum.
You see that's why it is important when asking question to provide DDL and INSERT scripts. If I had that then I would have gotten the error myself and would have modified the query by converting to int
So instead of this (simplified)
SELECT SUM(col1)
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
I would have done this
SELECT SUM(CONVERT(INT,col1))
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
And of course we should all read this-->
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.htm l
Does this qualify as a rant? I hope not.
3 comments:
Little bit of a rant. But understandable :)
I also question as to why not allow bits to be summed. BOL calls it an integer type with a range from 0 to 1 + null. Integers can be summed.
I suggested it here. Vote for or against as desired...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=201571
Well you got my vote ;-)
Select sum(case when Active=1 then 1 else 0 end) as SumofActive,
count(*) as totalnumberofrecords
from ....
Using Case will work, too. The Column Active is set to a bit and you may actaully use True or False..
I say continue with this work around.. no telling what they would break if they allowed it to sum or average.
Bit is a boolean and using the sum or average changes it's formatting to more that one space.
Probably would alter the Cosmos in a very bad way.
Post a Comment