SQL Numeric Functions

Numeric functions let you perform math directly inside a SQL query — rounding prices, taking absolute values, classifying positive versus negative amounts, or generating random numbers — without pulling data into application code first. They matter because raw stored numbers are rarely in the shape you want to display or compare: a price might carry five decimal places, a balance might need its sign checked, or an average needs to be rounded to two decimals for a report. SQL numeric functions handle all of this at the database layer, close to the data, which is faster and less error-prone than post-processing in your application code.

Overview: How Numeric Functions Work

Every numeric function takes one or more numeric expressions — a column, a literal, or the result of arithmetic — and returns a single numeric value. Most numeric functions are scalar: the database engine calls them once per row, independently, as it builds each output row. This is different from aggregate functions like SUM(), AVG(), or COUNT(), which collapse many rows into one value. Some function names overlap in a confusing way — MIN() and MAX() behave as aggregate functions when called with a single column argument over many rows, but as scalar functions when called with two or more arguments compared within a single row (covered in the Under the Hood section below).

Under the hood, SQLite — like other engines — stores numbers using type affinity rather than one rigid numeric type: a value is physically stored as an INTEGER, a REAL (an 8-byte IEEE-754 floating point number), or occasionally TEXT/NUMERIC, whichever best represents it. This matters for numeric functions because the storage class of the input often decides the storage class of the output. 17 / 5, for example, divides two integers, so SQLite performs integer division and truncates the result toward zero, returning 3 — not 3.4. If either operand is a REAL (as in 17.0 / 5), the whole expression is promoted to floating-point division. This single rule is behind more "why is my average wrong" bugs than almost anything else in SQL.

Because most engines (SQLite, MySQL, PostgreSQL, SQL Server) store floating-point numbers as IEEE-754 doubles, decimal fractions like 0.1 or 0.2 cannot always be represented exactly in binary. This is one reason functions like ROUND() exist — not just for display formatting, but to intentionally collapse tiny binary representation error before you compare or present a value.

In the logical query-processing order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT), scalar numeric functions are evaluated wherever their expression appears — inside WHERE, inside the SELECT list, or inside ORDER BY. When a numeric function wraps an aggregate, such as ROUND(AVG(salary), 2), the aggregate is computed first for each group, and the scalar function is applied only once to that single aggregated result — never to every underlying row.

Syntax

Numeric functions are called like any SQL function: the function name immediately followed by parentheses containing zero or more arguments. Arithmetic operators are written inline between operands. The table below covers the numeric functions and operators you will reach for constantly (all verified against SQLite; notes on other dialects follow).

Function / Operator	Description	Example
ABS(X)	Returns the absolute (non-negative) value of X	ABS(-7) returns 7
ROUND(X)	Rounds X to the nearest whole number	ROUND(4.6) returns 5.0
ROUND(X, Y)	Rounds X to Y digits after the decimal point	ROUND(4.567, 2) returns 4.57
SIGN(X)	Returns -1, 0, or 1 depending on whether X is negative, zero, or positive	SIGN(-42) returns -1
RANDOM()	Returns a pseudo-random signed integer, different on every call	RANDOM() returns e.g. -3847562910128
MIN(X, Y, ...)	Scalar form: smallest of a list of values on one row	MIN(4, 9, 1) returns 1
MAX(X, Y, ...)	Scalar form: largest of a list of values on one row	MAX(4, 9, 1) returns 9
+ - * /	Standard arithmetic operators	17 / 5 returns 3 (integer division)
%	Modulo — remainder after integer division	17 % 5 returns 2

Try a few of these together:

SELECT ABS(-7) AS absolute_value, ROUND(4.567, 2) AS rounded_value, SIGN(-42) AS sign_value;

Result: absolute_value = 7, rounded_value = 4.57, sign_value = -1.

Other dialects offer more built-in numeric functions than a default SQLite installation. MySQL and PostgreSQL both ship FLOOR(), CEIL()/CEILING(), POWER(), and SQRT() as standard functions, and SQL Server's ROUND() and ABS() behave almost identically to what you see here. Some SQLite builds enable an optional math extension that adds SQRT(), POWER(), FLOOR(), and CEILING(), but because that is a compile-time option rather than a guarantee, this lesson sticks to numeric functions available in every standard SQLite installation.

Examples

Example 1: Rounding Prices and Taking Absolute Values

A product catalog often stores prices with more decimal precision than you want to display, and discount amounts as negative numbers you may need to present as positive magnitudes.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price REAL,
  discount REAL
);

INSERT INTO products VALUES
  (1, 'Keyboard', 45.678, -5.5),
  (2, 'Monitor', 249.234, -12.5),
  (3, 'Mouse', 19.999, 0);

SELECT name, price, ROUND(price, 2) AS rounded_price, ABS(discount) AS discount_magnitude
FROM products;

name	price	rounded_price	discount_magnitude
Keyboard	45.678	45.68	5.5
Monitor	249.234	249.23	12.5
Mouse	19.999	20.0	0.0

Notice the Mouse row: 19.999 rounded to two decimals carries over to 20.0, not 19.99, because 19.999 is closer to 20.00 than to 19.99. ABS() simply strips the negative sign from each discount so it can be shown as a plain magnitude, for example in the phrase "You saved $12.50".

Example 2: Classifying Values with SIGN()

SIGN() is useful whenever you need to bucket rows into positive, negative, or zero without writing a verbose CASE expression.

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  description TEXT,
  amount REAL
);

INSERT INTO transactions VALUES
  (1, 'Paycheck', 1500.00),
  (2, 'Groceries', -85.50),
  (3, 'Refund', 0);

SELECT description, amount, SIGN(amount) AS sign_value
FROM transactions;

description	amount	sign_value
Paycheck	1500.0	1
Groceries	-85.5	-1
Refund	0.0	0

Each row gets exactly one of three outcomes. In a real application you might wrap this in a CASE to label rows as 'deposit', 'withdrawal', or 'no change', but SIGN() gives you the raw classification in a single, indexable expression.

Example 3: Rounding an Aggregate Result

Numeric functions frequently wrap an aggregate rather than a raw column — here we compute an average salary per department and round only the final group result, not each individual salary.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees VALUES
  (1, 'Ana', 'Engineering', 72000),
  (2, 'Ben', 'Engineering', 68500),
  (3, 'Cara', 'Sales', 54000),
  (4, 'Dan', 'Sales', 59000),
  (5, 'Eve', 'Marketing', 61000);

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY department;

department	avg_salary
Engineering	70250.0
Marketing	61000.0
Sales	56500.0

SQLite groups the five rows into three department buckets, computes AVG(salary) once per bucket, and only then applies ROUND(..., 2) to that single averaged number. The result is one row per department, sorted alphabetically by the ORDER BY clause.

How It Works Step by Step

It helps to see the difference between the scalar and aggregate forms of the same function name side by side:

SELECT MIN(12, 4, 9) AS row_wise_min, MAX(12, 4, 9) AS row_wise_max;

Result: row_wise_min = 4, row_wise_max = 12. Here MIN() and MAX() are given three literal arguments and compare them within a single row — no GROUP BY, no multiple rows involved. Compare that to SELECT MIN(salary) FROM employees, which is the aggregate form: it scans every row in the table and reduces them all to one value. Same function name, two entirely different behaviors depending on whether you pass multiple arguments on one row or one column across many rows.

Walking through Example 3's query in logical processing order: FROM employees supplies the five source rows; there is no WHERE clause, so none are filtered; GROUP BY department partitions the rows into Engineering, Sales, and Marketing buckets; AVG(salary) is evaluated once per bucket during this grouping step; the SELECT list then applies ROUND(..., 2) to each bucket's average and projects department alongside it; finally ORDER BY department sorts the three resulting rows alphabetically. The numeric function only ever touches the already-aggregated value — it never sees the original five salary rows individually.

Common Mistakes

Mistake 1: Expecting Integer Division to Produce a Decimal

This is the single most common numeric surprise in SQL. When both operands of / are integers, SQLite performs integer division and discards the remainder.

SELECT 17 / 5 AS average;

Result: average = 3, not 3.4. The fix is to force at least one operand to be a floating-point value, either by writing a decimal literal or casting:

SELECT 17.0 / 5 AS average;

Result: average = 3.4. When dividing two integer columns, use CAST(numerator AS REAL) / denominator for the same effect.

Mistake 2: Assuming a Negative Second Argument to ROUND() Rounds to Tens or Hundreds

In MySQL and PostgreSQL, ROUND(1250, -2) rounds to the nearest hundred, returning 1300. SQLite does not support negative rounding digits the same way — a negative or omitted second argument is treated as zero.

SELECT ROUND(1250, -2) AS rounded_hundreds;

Result: rounded_hundreds = 1250.0 — unchanged, because SQLite silently treats -2 as 0 decimal places rather than rounding to the hundreds place. If you need to round to the nearest hundred in SQLite, do the scaling yourself: ROUND(1250 / 100.0) * 100.

Mistake 3: Comparing Floating-Point Results Directly with =

Because 0.1 and 0.2 cannot be represented exactly as binary doubles, adding them does not produce a value that is bit-for-bit equal to the literal 0.3.

SELECT (0.1 + 0.2) = 0.3 AS are_equal;

Result: are_equal = 0 (false), even though the two sides are mathematically equal. Round both sides to a sane precision before comparing:

SELECT ROUND(0.1 + 0.2, 10) = ROUND(0.3, 10) AS are_equal;

Result: are_equal = 1 (true). This pattern — rounding before an equality check — is worth remembering any time you compare computed floating-point values rather than values read straight from storage.

Best Practices

• Force floating-point division explicitly (column * 1.0, a decimal literal, or CAST(... AS REAL)) whenever you need a fractional result, rather than relying on implicit promotion.
• Round only at the presentation layer — store full precision and apply ROUND() in the final SELECT, so intermediate calculations do not compound rounding error.
• Never compare computed floating-point expressions with =; round both sides to a fixed precision first, or compare within a small tolerance.
• Remember that ROUND()'s negative-digit behavior differs across databases — test the exact dialect you are targeting rather than assuming MySQL/PostgreSQL semantics apply everywhere.
• Prefer SIGN() over a multi-branch CASE expression when you only need to know positive, negative, or zero.
• When rounding an aggregate, wrap the aggregate (ROUND(AVG(x), 2)) rather than rounding each row before aggregating — rounding first shifts your average.
• Avoid wrapping an indexed column in a numeric function inside a WHERE clause (e.g. WHERE ROUND(price) = 20); it usually prevents the engine from using an index on that column.

Practice Exercises

1. Using a table like the transactions table from Example 2, write a query that returns each transaction's description alongside the absolute value of its amount, aliased as amount_abs.
2. Using the products table from Example 1, write a query that returns name and price along with a whole-number rounded price aliased as display_price. Check your result against all three rows, including the Mouse row where rounding carries over to the next whole number.
3. Using the employees table from Example 3, write a query returning each employee's name, salary, and a column salary_vs_70k computed as SIGN(salary - 70000). What do the three possible results (1, 0, -1) mean for each employee?

Summary

• Numeric functions are mostly scalar — evaluated once per row — while functions like SUM(), AVG(), and single-argument MIN()/MAX() are aggregate, collapsing many rows into one.
• ABS(X) strips a sign, ROUND(X, Y) rounds to Y decimal digits, and SIGN(X) reports -1, 0, or 1.
• Integer division truncates toward zero in SQLite; use a decimal literal or cast to get a fractional result.
• SQLite treats a negative or omitted second argument to ROUND() as zero — it does not round to tens or hundreds the way MySQL and PostgreSQL do.
• Floating-point results should never be compared with = directly; round both sides first.
• When a numeric function wraps an aggregate, the aggregate is computed per group first, then the function is applied once to that result.