Skip to content Skip to main navigation Skip to footer

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.
MOD

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *