Microsoft sql query error

try this

select CAST((CAST(SUM([Del]) AS DECIMAL) / COUNT([Del])) AS DECIMAL(36, 4)) AS [Del_%]
from dbo.TEMP_vw

Select allOpen in new window

I have tried with above query and still I'm getting same error.

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

can you post few rows from this table ?

Less code a without  CASE keyword^:

select SUM(CAST(Del AS DECIMAL(36,4)))/ COUNT(Del) [Del_%]
from dbo.TEMP_vw

Select allOpen in new window

Thanks Vitor Montalvão and Vishal Patil for your help. Now I am able to execute above query without error.

I am marking this case as closed.

If your column type is bit then you should check for the values 'True'  or 'False'. Bit column wont return you the values 1 or 0 directly.

Please try following query. If this wont work then we need to check the structure of your table.

select CAST(CAST(SUM(case [Del] when 'True' then 1 else 0 end) AS DECIMAL) / COUNT([Del]) AS DECIMAL(36, 4)) AS [Del_%]
from dbo.TEMP_vw

Select allOpen in new window

@Vishal

Bit datatype means binary values, so 0 and 1 should be the accepted values.
Boolean datatype accepts TRUE and FALSE but SQL Server doesn't have a Boolean datatype. And as you can see you need to write 'True' and 'False' as VARCHAR so SQL Server can convert it to bit.

Forgot to post the MSDN article