How can i get decimal values in of a column

Question

I have this query where i am getting average of a time i want to get the value in decimal 

select Call_assign,
count([Call_assign]) as CallsReceived,(avg(datediff(ss, start_time,end_time))/60) as AvgMinutes,((avg(datediff(ss, start_time, end_time))/60)/count([Call_assign])) 
 as AveragePerRecord
from [Technical].[dbo].[Tech] where  
date_time between '2013-12-1' and '2013-12-25'   
group by Call_assign

Call_assign CallsReceived  AvgMinutes AveragePerRecord
Jon                             25       174             6
Alex                            26          1431                   55
mark                           44          580                     13
Fatima                        32          320                     10

I want AvgMinutes and AveragePerRecord in full calculated for like 25 divide by 174 is 6.96 but its displaying only  6 how can i show it in decimal form 

---

New born in developing

Answer 1

Cannot test it right now...But try the below query
select Call_assign,
count([Call_assign]) as CallsReceived,CAST(avg(datediff(ss, start_time,end_time))/60 AS DECIMAL(5,2)) as AvgMinutes,((avg(datediff(ss, start_time, end_time))/60)/count([Call_assign])) 
 as AveragePerRecord
from [Technical].[dbo].[Tech] where  
date_time between '2013-12-1' and '2013-12-25'   
group by Call_assign

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

When both the numerator and denominator are of INT type, the result resulted will be also of INT type and the decimal part is ignored.

Either cast your values or multiply with 1.0 so that the type can be changed.

Declare @numerator int=174,@denominator int=25 
select @numerator/@denominator
--try
select cast(@numerator as decimal(10,2))/@denominator
select @numerator/cast(@denominator as decimal(10,2))
select (@numerator*1.0)/@denominator
select @numerator/(@denominator*1.0)

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

This could be an easy solution 

,(avg(datediff(ss, start_time,end_time))/60.0) as AvgMinutes

---

Satheesh
My Blog

Answer 4

When you divide two integers in SQL Server you get a return type of integer.  This is why you get 6, instead of 6.96.  To get a decimal value, you must change one of the data types to decimal or float.

In your example, the simplest way to do that is to change "/60" to "/60.0". 

Please see:

http://technet.microsoft.com/en-us/library/ms175009.aspx

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

Answer 5

See

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs