Lecture 47: Working with: Numeric Functions

This lecture covers common SQL numeric functions. Just as we can manipulate text with string functions, SQL provides a variety of numeric functions to perform calculations, rounding, and other mathematical operations on numerical data. These are essential for reporting and data analysis.

Table of Contents

Common Numeric Functions

Let’s look at some of the most useful numeric functions in SQLite.

`ROUND(number, decimals)`

Rounds a number to a specified number of decimal places. If `decimals` is omitted, it rounds to the nearest integer.

SELECT ROUND(3.14159, 2); -- Result: 3.14
SELECT ROUND(10.7);       -- Result: 11.0
SELECT ROUND(10.3);       -- Result: 10.0

`ABS(number)`

Returns the absolute (positive) value of a number.

SELECT ABS(-10);  -- Result: 10
SELECT ABS(5);    -- Result: 5

`CEIL(number)` or `CEILING(number)`

Returns the smallest integer greater than or equal to the given number (rounds up).

SELECT CEIL(4.1);   -- Result: 5.0
SELECT CEIL(4.9);   -- Result: 5.0
SELECT CEIL(4.0);   -- Result: 4.0

`FLOOR(number)`

Returns the largest integer less than or equal to the given number (rounds down).

SELECT FLOOR(4.1);  -- Result: 4.0
SELECT FLOOR(4.9);  -- Result: 4.0
SELECT FLOOR(4.0);  -- Result: 4.0

`RANDOM()`

Returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

SELECT RANDOM();

To get a random number within a specific range, you often combine it with other arithmetic operations.

`MAX(x, y, …)` and `MIN(x, y, …)` (Non-Aggregate)

These versions of `MAX` and `MIN` are different from the aggregate functions we learned earlier. These take multiple arguments and return the largest or smallest among them, not across a column.

SELECT MAX(10, 20, 5); -- Result: 20
SELECT MIN(10, 20, 5); -- Result: 5

Arithmetic Operators

Don’t forget the basic arithmetic operators you can use directly in your `SELECT` statements:

`+` (Addition)
`-` (Subtraction)
`*` (Multiplication)
`/` (Division)
`%` (Modulo – remainder after division)

SELECT (10 + 5) * 2; -- Result: 30
SELECT 17 / 5;       -- Result: 3.4
SELECT 17 % 5;       -- Result: 2

Why are these useful?

Numeric functions are crucial for:

Calculations: Performing complex math on your data.
Formatting: Rounding currency values, for example.
Data Generation: Creating random numbers for simulations or test data.

In the Next Lecture…

We will tackle another important data type: dates and times. SQL provides special functions to extract parts of dates, calculate differences, and format them for display.

Lecture 46: Working with Text: String Functions