Solved

Microsoft sql query error

Posted on 2014-09-23
10
355 Views
Last Modified: 2014-09-23
Hi Experts,

I am not able to execute attached query because of below error. Could you please advise how to fix below error.

Error:
Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.
select CAST(CAST(SUM([Del]) AS DECIMAL) / COUNT([Del]) AS DECIMAL(36, 4)) AS [Del_%]
from dbo.TEMP_vw

Open in new window

0
Comment
Question by:sqldba2013
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
try this

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

Open in new window

0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
If Del is a bit column then you can't use SUM directly on this field. You'll need something like this:
select CAST(SUM(CASE Del
		WHEN 1 THEN 1
		ELSE 0
		END) / COUNT(Del) AS  DECIMAL(36, 4)) [Del_%]
from dbo.TEMP_vw

Open in new window

0
 
LVL 2

Assisted Solution

by:Vishal Patil
Vishal Patil earned 250 total points
Comment Utility
Hello sqldba2013,

If you are using bit column you can not directly apply Sum operation on it. Try with following query.

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

Open in new window

0
 

Author Comment

by:sqldba2013
Comment Utility
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.
0
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
can you post few rows from this table ?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Less code a without  CASE keyword^:
select SUM(CAST(Del AS DECIMAL(36,4)))/ COUNT(Del) [Del_%]
from dbo.TEMP_vw

Open in new window

0
 

Author Closing Comment

by:sqldba2013
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Vishal Patil
Comment Utility
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

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
@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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Forgot to post the MSDN article
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now