# Number Functions in SQL

## Objectives

This tutorial covers the following objectives:

- Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query
- Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value
- State the implications for business when applying TRUNC and ROUND to numeric values

## Purpose

- One of the reasons we put our money in a bank is to take advantage of the interest it accumulates over time.
- Banks adjust the interest rate with various economic indicators such as inflation and the stock market.
- Typically, interest rates are expressed as a percent such as 3.45%.
- If a bank decided to round the percentage rate to 3.5%, would it be to your advantage?
- If it decided to just drop the decimal values and calculate the interest at 3%, would you be happy then?
- Rounding and truncating numbers play an important part in business and in turn with the databases that support these businesses as they store and access numeric data.

## Number Functions

- The three number functions are:
**ROUND****TRUNC****MOD**

### ROUND

- ROUND can be used with both numbers and dates.
- It is mainly used to round numbers to a specified number of decimal places, but it can also be used to round numbers to the left of the decimal point.
- Syntax:

`ROUND(column|expression, decimal places)`

- Note that if the number of decimal places is not specified or is zero, the number will round to no decimal places.
- ROUND(45.926) 46
- ROUND(45.926, 0) 46
- If the number of decimal places is a positive number, the number is rounded to that number of decimal places to the right of the decimal point.
- ROUND(45.926, 2) 45.93
- If the number of decimal places is a negative number, the number is rounded to that number of decimal places to the left of the decimal point.
- ROUND(45.926, -1) 50

### TRUNC

- The TRUNC function can be used with both numbers and dates. It is mainly used to terminate the column, expression, or value to a specified number of decimal places.
- When TRUNC is used, if the number of decimal places is not specified, then like ROUND, the specified number defaults to zero.
- Syntax:

`TRUNC(column|expression, decimal places)`

- TRUNC (45.926, 2) 45.92
- As with ROUND, if the TRUNC expression does not specify the number of decimal places or specifies a zero, the number is truncated to zero decimal places.
- TRUNC (45.926, 0) 45
- TRUNC (45.926) 45
- Remember that TRUNC does not round the number.
- It simply terminates the number at a given point.

### MOD

- The MOD function finds the remainder after one value is divided by another value.
- For example, the MOD of 5 divided by 2 is 1.
- MOD can be used to determine whether a value is odd or even. If you divide a value by 2 and there is no remainder, the number must be an even number.

- For example, if the MOD of x divided by 2 is 0, then x must be an even number.
- The “Mod Demo” column will show if number of airports for each country is an odd or even number.

```
SELECT country_name, MOD(airports,2)
AS "Mod Demo"
FROM wf_countries;
```

- 1 means the number is odd, and zero means that it is even.

## Terminology

Key terms used in this tutorial included:

- Number functions
- MOD
- ROUND
- TRUNC

## Summary

In this tutorial, you should have learned how to:

- Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query
- Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value
- State the implications for business when applying TRUNC and ROUND to numeric values

## 0 Comments