# How to Use Numeric Functions in SQL Correctly

Numeric functions in SQL are most popular in banking projects. The Popular functions are Round, Mod, and Truncated or Truncate. Below is the example SQL query.

The below real-time example gives you good idea on MOD,ROUND and TRUNC functions quickly so that you can apply in your project too.

Also read: LTRIM and RTRIM best examples

The usual ROUND() functions uses the scientific method, but you can use the MOD() function to implement the commercial method. Mostly used in all banking projects this kind of conversion.

Assume a simple list of two decimal place numbers we wish to round to one decimal place.

SQL Query to create table

```
CREATE TABLE Accounts (amount DECIMAL(5,2) NOT NULL);

alter session set nls_numeric_characters='.,'

INSERT INTO ACCOUNTS(AMOUNT) VALUES(100.20);

INSERT INTO ACCOUNTS(AMOUNT) VALUES(300.20);

INSERT INTO ACCOUNTS(AMOUNT) VALUES(400.30);

SELECT * FROM ACCOUNTS;

```

Data in Table

AMOUNT
100.2
300.2
400.3

SQL query ROUND and MOD functions

```
SELECT amount, ROUND(amount, 1) AS scientific,

CASE WHEN MOD((100 * amount), 10) != 5

THEN ROUND(amount, 1)

ELSE
CASE WHEN MOD (FLOOR(MOD((10 * amount), 10)), 2) = 0

THEN TRUNC(amount, 1) -- even, down

ELSE ROUND(amount, 1) -- odd, up

END

END AS commercial

FROM Accounts;

```

Round function The result is as follows

AMOUNT,SCIENTIFIC,COMMERCIAL
100.2,100.2,100.2
300.2,300.2,300.2
400.3,400.3,400.3 