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.

Note: The modulus operator is used in many programming languages, and returns the remainder after a division operation.

• 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