SQLite | SQLite Functions | Rounding Numbers (round Function)

Tags:

• SQLite
• Database

The round function rounds a number. This article explains how to use it.

Using the round Function

The syntax is as follows.

round(number)
round(number, digits)

With one argument, the function rounds the specified number to the nearest integer. The second argument specifies the number of decimal places. For example, 2 rounds the number to two decimal places.

round(0.47);      /* 0.0 */
round(0.536);     /* 1.0 */
round(0.536, 0);  /* 1.0 */
round(0.536, 1);  /* 0.5 */
round(0.536, 2);  /* 0.54 */

The number of digits cannot be negative. When a column name is supplied, the function rounds each value stored in that column.

–

Create a table for the example.

create table point (id integer, point real);

sqlite> create table point (id integer, point real);
sqlite> 

Insert the following data.

insert into point values (1, 15.4853);
insert into point values (2, 27.143);
insert into point values (3, 38.902);
insert into point values (4, 26.5521);
insert into point values (5, 30.36);

sqlite> insert into point values (1, 15.4853);
sqlite> insert into point values (2, 27.143);
sqlite> insert into point values (3, 38.902);
sqlite> insert into point values (4, 26.5521);
sqlite> insert into point values (5, 30.36);
sqlite> 

Use round to round the values in the point column to the nearest integer.

select id, point, round(point) from point;

sqlite> .mode column
sqlite> .header on
sqlite> 
sqlite> select id, point, round(point) from point;
id          point       round(point)
----------  ----------  ------------
1           15.4853     15.0        
2           27.143      27.0        
3           38.902      39.0        
4           26.5521     27.0        
5           30.36       30.0        
sqlite> 

The output contains each value rounded to the nearest integer.

Next, round the values to one and two decimal places.

select id, point, round(point, 1), round(point, 2) from point;

sqlite> select id, point, round(point, 1), round(point, 2) from point;
id          point       round(point, 1)  round(point, 2)
----------  ----------  ---------------  ---------------
1           15.4853     15.5             15.49          
2           27.143      27.1             27.14          
3           38.902      38.9             38.9           
4           26.5521     26.6             26.55          
5           30.36       30.4             30.36          
sqlite> 

The output contains each value rounded to the specified number of decimal places.