SQL avg then trailing numbers

Question

I'm trying to get the average number, and then remove the trailing, pointless zeros afterwards, (new to SQL) but I can't understand why it wont remove them, do I have the wrong idea??

So far I have;

 SELECT total,
 AVG(total(TRUNCATE(total/1,2))

Answer 1

I think you are looking for cast as below.

select cast(17.800000 as dec(3,1))

Result:

val
----
17.8

so you query will be

SELECT total, cast(AVG(total) as dec(3,1))

considering you just need 2 digit before . If you need more digits, you can adjust it accordingly.

DEMO

Answer 2

Assuming you are using SQL Server then you can cast the answer to a decimal with one decimal point:

select cast(avg(total) as decimal(9,1))

Answer 3

This SQLFiddle shows it: link

SELECT 
 TRUNCATE(AVG(myFloat), 2), 
 AVG(myFloat), 
 ROUND(AVG(myFloat), 2)
 FROM docs

You should probably use ROUND instead of TRUNCATE.

The stuff after the decimal is odd because of floating point math, and there are occasions where floating point math is internally calculated as .009999999 instead of .01000000000

I believe these answers that use a CAST may have the same truncation problem. You simply want to avoid casting or truncation when you are removing the decimal places beyond what you're interested in. Be explicit in what you are doing and less mistakes will pop up later.