CODEWITHSUNDEEP
sqlsql-server
user1777929
user1777929

Reputation: 797

How to get average in only 2 decimals using SQL Server 2014?

I have a table that gives the time to answer a call in seconds (without decimals). I would like to get the average time to answer (in seconds with two decimals) per month. I wrote code but I'm getting 6 decimals.

Where am I going wrong?

My code:

SELECT datepart(year,CallStartTime) [Year]
      ,datepart(month,CallStartTime) [Month]
      ,datepart(day,CallStartTime) [Day]
      ,AVG( CAST((TimeToAnswer) AS Decimal (10,2))) [Average]

FROM LineCallTracer LCT

WHERE LCT.CallStartTime >= '2017-10-01 00:00:00.000'
  AND LCT.DNIS = 5050
  AND LCT.MainDevice IS NOT NULL

GROUP BY datepart(year,CallStartTime), 
    datepart(month,CallStartTime), 
    datepart(day,CallStartTime)

ORDER BY [Year], [Month], [Day]

The results I'm getting are as follows:

Year    Month   Day   Average
2017    10       1    0.466666
2017    10       2    1.513036
2017    10       3    1.642029
2017    10       4    0.890945
2017    10       5    0.541231
2017    10       6    0.638501

How do I get the Average in only two decimals?

EDIT: When I instead try two other options

,CAST( AVG(TimeToAnswer) AS Decimal (10,2)) [Average] 
           -- OR
,FORMAT(AVG(TimeToAnswer), '#########0.00') AS [Average]

then I get the following results (which is not accurate):

Year    Month   Day      Average
2017    10       1       0.00
2017    10       2       1.00
2017    10       3       1.00
2017    10       4       0.00
2017    10       5       0.00
2017    10       6       0.00

Upvotes: 1

Views: 3993

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Do the cast() after the average:

  CAST(AVG(TimeToAnswer) AS Decimal(10, 2)) as [Average]

The rules for the results of decimal arithmetic are rather convoluted. In general, with division, the results often have greater precision than the inputs.

If TimeToAnswer is an integer, then SQL Server does integer arithmetic. I would simply do:

  CAST(AVG(TimeToAnswer*1.0) AS Decimal(10, 2)) as [Average]

Upvotes: 2

user1777929
user1777929

Reputation: 797

I tried the following syntax and it worked. I used a mix of suggestions from @GordonLinoff and @ThorstenKettner. I have upvoted both their answers.

,FORMAT (AVG( CAST((TimeToAnswer) AS Decimal (10,2))), '#########0.00') [Average]

I am now getting the following results with automatic rounding up or down as necessary.

Year    Month   Day      Average
2017    10       1       0.47
2017    10       2       1.51
2017    10       3       1.64
2017    10       4       0.89
2017    10       5       0.54
2017    10       6       0.64

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

Use FORMAT to display a number in a desired format (e.g. with two decimal places):

FORMAT(AVG(TimeToAnswer), '#########0.00') AS [Average]

Upvotes: 1

Mahmod Abu Jehad
Mahmod Abu Jehad

Reputation: 177

SELECT datepart(year,CallStartTime) [Year]
      ,datepart(month,CallStartTime) [Month]
      ,datepart(day,CallStartTime) [Day]
      ,CAST(AVG( (TimeToAnswer)) AS Decimal (10,2)) [Average]

FROM LineCallTracer LCT

WHERE LCT.CallStartTime >= '2017-10-01 00:00:00.000'
  AND LCT.DNIS = 5050
  AND LCT.MainDevice IS NOT NULL

GROUP BY datepart(year,CallStartTime), 
    datepart(month,CallStartTime), 
    datepart(day,CallStartTime)

ORDER BY [Year], [Month], [Day]

Upvotes: 1

Related Questions