locked
How can i get decimal values in of a column RRS feed

  • 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


    Wednesday, December 25, 2013 10:07 AM

Answers

  • This could be an easy solution 

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


    Satheesh
    My Blog


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

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

All replies

  • 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

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

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

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


    Satheesh
    My Blog


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

    • Marked as answer by Fanny Liu Thursday, January 2, 2014 6:03 AM
    Thursday, December 26, 2013 1:27 PM
    Answerer
  • 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

    Thursday, December 26, 2013 4:27 PM
    Answerer