Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 125 ยท Functions and Expressions

Numeric Functions

Round prices, round up and down, take absolute values, divide without surprises and use powers, roots, logs and random numbers in SQL, with dialect notes.

Numbers in a database are rarely in the exact shape you need. A 15% discount produces prices like 29.665, a monthly salary needs two decimals, and a planning report needs to round up because half a box still needs a whole box. Numeric functions handle these jobs right in the query.

This lesson covers rounding (round, ceil, floor, truncation), signs and absolute values, integer division and remainders, powers, roots and logarithms, and random numbers. Along the way you'll meet the floating-point surprises that trip up almost every developer once, and learn how to avoid them.

All of these functions work on any numeric expression, so they combine naturally with the arithmetic from Calculated Columns and the operators from SQL Operators.

Rounding with round()

round(x) rounds to the nearest whole number and round(x, digits) keeps that many decimal places. Here every product gets a 15% discount, first unrounded, then rounded to cents and to whole units:

SQL Rounding sale prices ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The raw_sale column shows why rounding matters: the fountain pen comes out as 26.349999999999998 instead of 26.35. That isn't a bug in SQLite; it's how binary floating-point numbers work, which the next section explains.

A few rules about SQLite's round():

  • Halves round away from zero: round(2.5) is 3 and round(-2.5) is -3. (Some languages use "banker's rounding", which would give 2.)
  • The result is always a floating-point value (real), even when it looks like a whole number. Use CAST(round(x) AS INTEGER) if you need a true integer; the Types and CAST lesson covers this.
  • Negative digits are treated as 0 in SQLite, so round(1234.5678, -2) is 1235. PostgreSQL, MySQL and SQL Server accept negative digits and return 1200. In SQLite, round to hundreds with round(x / 100.0) * 100.

Floating-point surprises

REAL values are stored in binary, and most decimal fractions (like 0.1) have no exact binary form. The stored value is the closest binary number, which is usually a hair above or below the decimal you typed. Most of the time you never notice, but sometimes the error shows:

SQL Floating-point rounding errors ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

0.1 + 0.2 = 0.3 is false (0), the sum of delivered orders' shipping comes out as 191.20000000000002, and round(29.665, 2) gives 29.66 because 29.665 is really stored as 29.66499999.... (sum() adds up a column; you'll study it in Aggregate Functions.)

Three habits keep you out of trouble:

  1. Round when you display, not in the middle of a calculation, so errors don't pile up.
  2. Never compare floats with =. Check whether the difference is tiny instead, as the close_enough column does.
  3. Store money as integer cents (1999 instead of 19.99) or use an exact DECIMAL/NUMERIC type in databases that have one. SQLite has no exact decimal type, which is why the Data Types lesson recommends integers for money.

Rounding up, rounding down and truncating

Sometimes "nearest" is the wrong rule:

Function Rule 2.7 -2.7
round(x) nearest, halves away from zero 3 -3
ceil(x) up, toward positive infinity 3 -2
floor(x) down, toward negative infinity 2 -3
CAST(x AS INTEGER) drop the fraction (toward zero) 2 -2

Notice the negative column: floor() and truncation agree for positive numbers but not for negative ones.

Rounding up is the rule for capacity planning. In the company database, each assignment has hours_per_week. How many 8-hour days does each one fill? Even 5 hours occupies a day on the calendar, so round up:

SQL Round up with ceil() ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

30 hours is 3.75 days: ceil() blocks 4 days and floor() counts 3 full days.

Function availability. ceil() and floor() belong to SQLite's optional math functions. The build on this site includes ceil, floor, sqrt, power, exp, log, log10, pi and the trigonometric functions, but not ceiling, trunc, pow, ln, log2 or mod. The official sqlite3 program (3.35 and later) has all of these. CAST(x AS INTEGER) truncates in every SQLite, and (a + b - 1) / b rounds a positive integer division up without any math functions.

Absolute values and signs

abs(x) drops the minus sign, and sign(x) returns -1, 0 or 1. Together they answer "how far" and "in which direction". For example, how far is each Home Office product's price from 50?

SQL abs() and sign() ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Sorting by abs(price - 50) puts the products closest to 50 first, no matter whether they're above or below it. That "nearest match" pattern works for prices, dates converted to numbers and sensor readings alike. (The -10.100000000000001 in the first row is the floating-point effect again: 39.9 isn't stored exactly. Wrap the expression in round(..., 2) before showing it to people.)

Integer division and the % operator

Dividing two integers in SQLite gives an integer: the fraction is thrown away. That's a frequent source of wrong reports. The company stores yearly salaries as integers, so dividing by 12 silently truncates:

SQL Integer division and remainders ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

For Yara, 157000 / 12 is 13083, but 157000 / 12.0 is 13083.333.... Making one side a decimal (12.0) or multiplying by 1.0 switches to real division.

% (modulo) returns the remainder: 157000 % 12 is 4. It's useful for splitting rows into groups (id % 2 = 0 for even IDs) or cycling through values. Be careful with edge cases:

SQL
SELECT
  7 / 2,     -- 3     integer division
  -7 / 2,    -- -3    truncates toward zero
  -7 % 3,    -- -1    the sign follows the left operand
  7.5 % 2,   -- 1     % converts both sides to integers first
  10 / 0,    -- NULL  no error in SQLite
  10 % 0;    -- NULL

Dividing by zero returns NULL in SQLite and MySQL, but raises an error in PostgreSQL and SQL Server. The COALESCE and NULLIF lesson shows the portable fix, x / NULLIF(y, 0).

Powers, roots and logarithms

Function Meaning Example Result
power(x, y) x to the power y power(2, 10) 1024
sqrt(x) square root sqrt(16) 4
exp(x) e to the power x exp(1) 2.718281828459045
log10(x) base-10 logarithm log10(1000) 3
pi() ฯ€ pi() 3.141592653589793
radians(x), degrees(x) convert angles radians(180) 3.141592653589793
sin, cos, tan, atan2, โ€ฆ trigonometry, in radians sin(radians(30)) 0.49999999999999994

sqrt() of a negative number returns NULL rather than failing.

Growth calculations use power(). If every project budget grows 4% per year, what will it be in three years?

SQL Compound growth with power() ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

420000 ร— 1.04ยณ is about 472443. Change 1.04 and 3 to model other rates and periods.

log() means different things in different databases. In the official SQLite math functions and in PostgreSQL, log(x) is base 10 and ln(x) is the natural logarithm. In MySQL, SQL Server and the SQLite build on this site, log(x) is the natural logarithm. Use log10(x) when you mean base 10; it means the same thing everywhere.

Random numbers

random() returns a random 64-bit integer, which can be negative. Two common recipes turn it into something useful:

  • abs(random()) % 6 + 1 is a whole number from 1 to 6, like a die.
  • ORDER BY random() LIMIT n picks n random rows.
SQL Random picks ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Press Run several times: you get different products and different rolls every time, so your results won't match anyone else's. ORDER BY random() has to generate a number for every row and sort them all, so it's fine for a few thousand rows but slow on millions. Large systems use sampling features instead (TABLESAMPLE in PostgreSQL and SQL Server).

For random identifiers, hex(randomblob(16)) returns 32 random hexadecimal characters, the raw material of a UUID. The Auto-Increment and Identity Columns lesson discusses UUID keys.

Smallest and largest of several values

With two or more arguments, max() and min() compare values across columns in the same row: max(4, 9, 2) is 9. This caps or floors a value, for example max(price - 10, 0) for a discount that can't go below zero. If any argument is NULL, the result is NULL.

With a single argument, max() and min() are aggregate functions that look down a column instead. You'll use them that way in Aggregate Functions. Most other databases call the multi-argument versions GREATEST() and LEAST().

Numeric functions in other databases

Task SQLite PostgreSQL MySQL SQL Server
Round to n places round(x, n) round(x, n) round(x, n) round(x, n)
Round up / down ceil / floor ceil / ceiling / floor ceil / ceiling / floor ceiling / floor
Truncate CAST(x AS INTEGER), trunc(x) trunc(x, n) truncate(x, n) round(x, n, 1)
Remainder %, mod(a, b) %, mod(a, b) %, mod(a, b) %
Integer รท integer integer result integer result decimal result (DIV for integer) integer result
Divide by zero NULL error NULL (warning) error
Power power / pow power power / pow power
Random random() (integer) random() (0 to 1) rand() (0 to 1) rand() (0 to 1)
Max of values max(a, b) greatest(a, b) greatest(a, b) greatest(a, b) (2022+)
SQL
-- PostgreSQL: random integer from 1 to 6, rounding to hundreds
SELECT floor(random() * 6 + 1)::int, round(1234.5678, -2);

-- MySQL: real division by default, integer division with DIV
SELECT 7 / 2, 7 DIV 2;   -- 3.5000, 3

-- SQL Server
SELECT CEILING(2.1), ROUND(1234.5678, -2), ABS(CHECKSUM(NEWID())) % 6 + 1;

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In SQLite, what does SELECT 157000 / 12; return?

    Both operands are integers, so SQLite performs integer division and drops the fraction. Write 157000 / 12.0 for a decimal result.
  2. 2. What does floor(-2.7) return?

    floor() always goes down, toward negative infinity, so -2.7 becomes -3. Truncation with CAST(-2.7 AS INTEGER) would give -2.
  3. 3. Why does 0.1 + 0.2 = 0.3 return 0 (false)?

    Binary floating-point numbers can only approximate most decimal fractions. Compare with a small tolerance or store exact values such as integer cents.
  4. 4. An order needs 30 bottles and a box holds 8. Which expression gives the number of boxes (4)?

    30 / 8.0 is 3.75 and a partial box still needs a box, so round up with ceil(). 30 / 8 is integer division and gives 3.
  5. 5. Which query returns 3 random products?

    ORDER BY random() shuffles the rows and LIMIT 3 keeps the first three of the shuffled order.

Summary

  • round(x, n) rounds halves away from zero and returns a real; SQLite ignores negative n.
  • Floating-point values are approximations: round for display, don't compare with =, and store money as integer cents.
  • ceil() rounds up, floor() rounds down, and CAST(x AS INTEGER) truncates toward zero.
  • abs() and sign() measure distance and direction; ORDER BY abs(x - target) finds the nearest values.
  • Integer รท integer is an integer in SQLite; use 12.0 for real division. % is the remainder, and dividing by zero gives NULL.
  • power(), sqrt(), exp() and log10() cover growth and geometry; log() is base 10 in some databases and natural in others.
  • abs(random()) % n + 1 and ORDER BY random() produce random values and samples.

Next: Date and Time Functions, where you'll calculate with dates and format them for reports.