Approximating and standardizing the numbers is one of the common data cleaning requirement before data analytics. In this blog we will discuss 3 useful functions for such requirements.

Before moving further the examples used are codes written on and outputs got on *Postgres shell*

**round() function for rounding float:**We often have float of various length in the dataset especially when values are converted from one unit to other. When it comes to data warehousing & analysis it is a good practice to have a uniformity among the quantities.**Round()**function comes in handy while dealing with float in SQL.

here is syntax:

```
round(<number/column>,<digits from decimal point>)
```

example:

```
test_1=# select round(15.4567,2) as round_value;
round_value
-------------
15.46
(1 row)
```

here in this example the number 15.4567 was passed to round function and requirement was to round the value for 2 places from decimal point.

The round function works exactly as mathematical approximation, if next digit is more than 5 last rounding digit will get incremented. We can see that in our example, we are rounding the number to 2 decimal points, digit next to that is 6 hence the number is rounded to 15.46. If it was less than 5 it would have remained 15.45,

Note: There is one programming concept you need to learn, called –* floating point error*

**ceil() function to have nearest higher integer:**There are other requirements where you need to approximate the float to nearest highest integer or next integer.

syntax:

```
ceil(<float/column>)
```

for example:

```
test_1=# select ceil(15.4567) as appr_value;
appr_value
------------
16
(1 row)
```

**floor() function to have a nearest lower integer:**Similar to ceil, If you have to approximate a numbers to nearest lowest integer then you will have to use floor function.

syntax:

```
floor(<float/column>)
```

example:

```
test_1=# select floor(15.4567);
floor
-------
15
(1 row)
```

**Comparison:**

Below is an example showing comparison between all three functions. I have table naming** test**

with one column called number and float values in it; below is a query with all three functions and their returns.

```
test_1=# select number, round((number::numeric),2), ceil(number), floor(number) from test;
number | round | ceil | floor
---------+-------+------+-------
15.4567 | 15.46 | 16 | 15
76.1234 | 76.12 | 77 | 76
15.1515 | 15.15 | 16 | 15
(3 rows)
```