SQLite floor() Function

Summary: in this tutorial, you will learn how to use the SQLite floor() function to return the first integer less than or equal to the input number.

Introduction to SQLite floor() function

The floor() function rounds a number to the first integer less than or equal to the input number.

Here’s the syntax of the floor() function:

floor(x)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• x is the number that you want to round. x can be a number, an expression, or a table column.

If x is positive, the function rounds it toward zero. If x is negative, the function rounds it away from zero.

The floor() function returns NULL if the input number (x) is NULL.

SQLite floor() function examples

Let’s explore some examples of using the floor() function.

1) Using the floor() function with a positive number

The following example uses the floor() function to round a positive number away from zero:

SELECT floor(2.6) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
2.0Code language: SQL (Structured Query Language) (sql)

2) Using the floor() function with a negative number

The following statement uses the floor() function to round a negative number toward zero:

SELECT floor(-2.6) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
-3.0Code language: SQL (Structured Query Language) (sql)

3) Using the floor() function with table data

We’ll use the tracks table from the sample database for the demonstration.

The following statement uses the floor() function to round the average length of tracks in minutes:

SELECT
  floor(avg(milliseconds / 60000)) average_length
FROM
  tracks;Code language: SQL (Structured Query Language) (sql)

Output:

average_length
--------------
6.0Code language: SQL (Structured Query Language) (sql)

Summary

• Use the floor() function to return the first integer less than or equal to the input number.