loading..
Русский    English
00:31

ROUND function

Let's consider the following task

Compute average capacity of PC hard drive getting the result with two decimal digits.

Having executed the query

Console
Execute
  1. SELECT AVG(hd) AS avg_hd FROM pc;
we'll obtain the result:

avg_hd
13.6666666666667

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

Console
Execute
  1. SELECT round(AVG(hd),2) AS avg_hd FROM pc;

avg_hd
13.67

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

Console
Execute
  1. SELECT round(AVG(hd),2,1) AS avg_hd FROM pc;
то получим другой результат:

avg_hd
13.66

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.

Console
Execute
  1. SELECT round(AVG(hd),-1) AS avg_hd FROM pc;

avg_hd
10

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,

avg_hd
13.670000

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

  1. 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. :-) 

Console
Execute
  1. SELECT CAST(AVG(hd) AS DEC(12,2)) AS avg_hd FROM pc;

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100