SQL numeric functions project example queries

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

Rounding

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

Also read

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.