CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
NateJ
NateJ

Reputation: 2005

SQL Server AVG with Decimals & Floats

Basically, two queries that do a similar averaging, are giving me different results; the "actual" value expected is 81.25, so with normal rounding, you would think that CAST-ing it to DECIMAL(10,1) would result in 81.3, but one of the queries (the first one) gives 81.2

Here's the examples:

CAST( AVG( t1.NumCorrect / 7.0 * 100.0 ) AS decimal(10,1))

vs.

CAST( AVG( t2.PercentCorrect ) AS decimal(10,1))

The only difference, as far as I can tell, is that PercentCorrect is type REAL, and NumCorrect is type FLOAT.. but those are both floating-point types, so why would casting them behave differently?

In the first line, NumCorrect is like a "# correct out of 7 possible", so I'm calculating a "Percent" on-the-fly, which is then AVG'd; whereas in the 2nd example, the Percent is pre-calculated, and I just need it AVG'd. Again, first line is the one that gives 81.2 instead of 81.3

If I need to provide more context with the surrounding queries or source-data, I can try... just let me know.

Upvotes: 1

Views: 3588

Answers (2)

user7021980
user7021980

Reputation: 11

The easiest way is to divide by 1.0 and it will produce 6 digit precision.

Example:

select avg(incomeamount/1.0),vendorid from dailyincome group by vendorid

Upvotes: 1

rkrdo
rkrdo

Reputation: 196

Execute this on SQL and you'll get your answer. 

select 1425554.3/5457.251


select convert(real, (1425554.3/5457.251))

Basically, t2.PercentCorrect is already casted/converted to real which truncates the decimal point values. t1.NumCorrect is divided on the fly and all decimal points are processed on the avg function.

Hope this helps!

Upvotes: 3

Related Questions