# 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
```

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

Wednesday, December 25, 2013 10:07 AM

• This could be an easy solution

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

Satheesh
My Blog

• Proposed as answer by Thursday, December 26, 2013 1:24 PM
• Marked as answer by Thursday, January 2, 2014 6:03 AM
Thursday, December 26, 2013 9:04 AM
• 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".

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.

• Marked as answer by Thursday, January 2, 2014 6:03 AM
Thursday, December 26, 2013 1:27 PM

### All replies

• ```Cannot test it right now...But try the below queryselect 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

Wednesday, December 25, 2013 11:59 AM
• 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)```

Thursday, December 26, 2013 8:01 AM
• This could be an easy solution

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

Satheesh
My Blog

• Proposed as answer by Thursday, December 26, 2013 1:24 PM
• Marked as answer by Thursday, January 2, 2014 6:03 AM
Thursday, December 26, 2013 9:04 AM
• 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".

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.

• Marked as answer by Thursday, January 2, 2014 6:03 AM
Thursday, December 26, 2013 1:27 PM
• See

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