ROUND function

Let’s consider the following task

Having executed the query

select AVG(hd) as avg_hd from pc;

To get needed format of output, we can use ROUND function:

select round(AVG(hd),2) as avg_hd from pc;

The second argument of the function determine the number of decimal places to which the result must be rounded.

The result demonstrates that we have arithmetic rounding. By the way, ROUND function has the third optional argument, which determines whether the result should be rounded (value of 0 - is default) or be truncated (the value other than 0).

I.e. if to rewrite the query as follows

select round(AVG(hd),2,1) as avg_hd from pc;

We can do rounding to any place, not only to a decimal one. For example, to get rounding to tens or hundreds and  so on, you can use negative value of second argument. The following query is rounding the result to tens.

select round(AVG(hd),-1) as avg_hd from pc;

It should be noted that ROUND function does rounding but does not change the result type. I.e. if the first argument is of the dec(12,6) type, the result of rounding  will be of the same type, namely,

It is easy to be convinced of it, having executed the query

select round(cast(AVG(hd) as dec(12,6)),2) as avg_hd from pc;

Therefore, if you wish to get rid of trailing zeroes, use conversion to the type required, for example, dec (12,2). Then we don’t need ROUND function at all. :-)

select cast(AVG(hd) as DEC(12,2)) as avg_hd from pc;