CODEWITHSUNDEEP
sqlaverage
ExceptionLimeCat
ExceptionLimeCat

Reputation: 6400

Arithmetic overflow error converting numeric to data type numeric. AVG Select Statment

I am trying to average the values for a given month using the AVG function but it's throwing the Arithmetic overflow error converting numeric to data type numeric. After some Googling, it looks like the common cause for this is trying to store the output of the AVG function into a variable/column that has a datatype not suitable for the incoming value. I am receiving this error just trying to SELECT the values.

--Hours (DECIMAL(18,4)), Starts (DECIMAL(18,4))
SELECT ID, Avg(Hours), Avg(Starts)
FROM MonthlyReadings
WHERE ReadingDate < '2016-03-01'
AND   ReadingDate > DATEADD(MM, -36, '2016-03-01')
AND   MONTH(ReadingDate) = 1
GROUP BY ID

Any idea why I'm getting this error?

Upvotes: 1

Views: 267

Answers (1)

Hart CO
Hart CO

Reputation: 34784

Resulting datatype is inherited from the field you're aggregating. You need a datatype large enough to hold the SUM() in order to calculate the AVG(), you should be fine with FLOAT or expand your DECIMAL:

SELECT ID, Avg(CAST(Hours AS FLOAT)), Avg(CAST(Starts AS FLOAT))
FROM MonthlyReadings
WHERE ReadingDate < '2016-03-01'
AND   ReadingDate > DATEADD(MM, -36, '2016-03-01')
AND   MONTH(ReadingDate) = 1
GROUP BY ID

Upvotes: 1

Related Questions