SQL Built-In Numeric Functions

Numbers drive business logic in every database. Prices need rounding for display, tax calculations require precise arithmetic, financial reports depend on correct totals, and analytics queries compute percentages, averages, and growth rates. Raw numeric data rarely arrives in the exact form you need, and the calculations you perform on it must be accurate.

SQL numeric functions let you round, truncate, compute absolute values, perform modular arithmetic, raise numbers to powers, and much more, all directly in your queries. Combined with basic arithmetic operators, they give you a complete mathematical toolkit that runs inside the database engine, close to the data, without pulling numbers into application code for processing.

This guide covers every essential numeric function with practical examples and outputs, explains arithmetic in SELECT statements, and highlights the pitfalls that trip up developers working with numbers in SQL.

The Sample Data

All examples use these tables:

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    price         DECIMAL(10,2),
    cost          DECIMAL(10,2),
    stock_qty     INT
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    subtotal      DECIMAL(10,2),
    tax_rate      DECIMAL(5,4),
    discount_pct  DECIMAL(5,2)
);

INSERT INTO products VALUES
(101, 'Wireless Mouse',      'Electronics', 25.99,  12.50, 150),
(102, 'USB-C Cable',          'Electronics',  9.99,   3.75, 300),
(103, 'Notebook A5',          'Stationery',   4.50,   1.80, 500),
(104, 'Mechanical Keyboard',  'Electronics', 89.99,  42.00,  45),
(105, 'Desk Lamp',            'Furniture',   34.50,  18.25,   0),
(106, 'Pen Pack',             'Stationery',   2.99,   0.95, 800),
(107, 'Standing Desk',        'Furniture',  299.99, 155.00,  12);

INSERT INTO orders VALUES
(1001, 1, '2024-06-01', 155.97, 0.0875, 10.00),
(1002, 2, '2024-06-03', 299.99, 0.0625,  0.00),
(1003, 1, '2024-06-05',  45.48, 0.0875,  5.00),
(1004, 3, '2024-06-07',  89.99, 0.1000, 15.00),
(1005, 2, '2024-06-10',   9.99, 0.0625,  0.00);

Arithmetic in SELECT

Before diving into functions, let's cover the foundation: SQL supports standard arithmetic operators directly in SELECT statements, WHERE clauses, and anywhere an expression is valid.

The Operators

Operator	Operation	Example	Result
+	Addition	10 + 3	13
-	Subtraction	10 - 3	7
*	Multiplication	10 * 3	30
/	Division	10 / 3	3 or 3.333... (depends on types)
%	Modulo (remainder)	10 % 3	1

Basic Arithmetic Examples

Calculate profit margin per product:

SELECT
    product_name,
    price,
    cost,
    price - cost AS profit,
    (price - cost) / price * 100 AS margin_pct
FROM products;

product_name	price	cost	profit	margin_pct
Wireless Mouse	25.99	12.50	13.49	51.90
USB-C Cable	9.99	3.75	6.24	62.46
Notebook A5	4.50	1.80	2.70	60.00
Mechanical Keyboard	89.99	42.00	47.99	53.33
Desk Lamp	34.50	18.25	16.25	47.10
Pen Pack	2.99	0.95	2.04	68.23
Standing Desk	299.99	155.00	144.99	48.33

Calculate total inventory value:

SELECT
    product_name,
    price,
    stock_qty,
    price * stock_qty AS inventory_value
FROM products
ORDER BY inventory_value DESC;

product_name	price	stock_qty	inventory_value
Wireless Mouse	25.99	150	3898.50
Standing Desk	299.99	12	3599.88
USB-C Cable	9.99	300	2997.00
Pen Pack	2.99	800	2392.00
Notebook A5	4.50	500	2250.00
Mechanical Keyboard	89.99	45	4049.55
Desk Lamp	34.50	0	0.00

Calculate order totals with tax and discount:

SELECT
    order_id,
    subtotal,
    discount_pct,
    subtotal * (1 - discount_pct / 100) AS after_discount,
    subtotal * (1 - discount_pct / 100) * (1 + tax_rate) AS final_total
FROM orders;

order_id	subtotal	discount_pct	after_discount	final_total
1001	155.97	10	155.97	169.61737499999998
1002	299.99	0	299.99	318.739375
1003	45.48	5	45.48	49.45949999999999
1004	89.99	15	89.99	98.989
1005	9.99	0	9.99	10.614375

The results have too many decimal places. This is where rounding functions become essential.

Integer Division Trap

The most common arithmetic mistake in SQL involves division between integers:

-- Integer division: result is truncated to integer in some databases
SELECT 10 / 3 AS result;

Database	10 / 3 Result
PostgreSQL	3 (integer division)
MySQL	3.3333 (returns decimal)
SQL Server	3 (integer division)
Oracle	3.3333... (returns decimal)
SQLite	3 (integer division)

danger

In PostgreSQL, SQL Server, and SQLite, dividing two integers performs integer division, truncating the decimal portion entirely. 10 / 3 returns 3, not 3.333.

Wrong:

SELECT 7 / 2 AS result;
-- PostgreSQL/SQL Server/SQLite: 3 (not 3.5!)

Fix: Cast at least one operand to a decimal type:

SELECT 7.0 / 2 AS result;           -- 3.5
SELECT CAST(7 AS DECIMAL) / 2;      -- 3.5
SELECT 7 / 2.0 AS result;           -- 3.5
SELECT 7::DECIMAL / 2;              -- 3.5 (PostgreSQL)

Division by Zero

Dividing by zero raises an error in most databases:

SELECT 100 / 0;
-- ERROR: division by zero

Protect against it:

-- Use NULLIF to turn 0 into NULL (division by NULL returns NULL, not error)
SELECT
    product_name,
    price / NULLIF(stock_qty, 0) AS price_per_unit_in_stock
FROM products;

product_name	price_per_unit_in_stock
Wireless Mouse	0.17
Desk Lamp	NULL

The Desk Lamp has stock_qty = 0. Without NULLIF, this query would crash. With NULLIF(stock_qty, 0), zero becomes NULL, and price / NULL safely returns NULL.

tip

NULLIF(a, b) returns NULL if a = b, otherwise returns a. Wrapping the divisor in NULLIF(divisor, 0) is the standard pattern for safe division in SQL.

ROUND: Rounding Numbers

ROUND rounds a number to a specified number of decimal places.

Syntax

ROUND(number, decimal_places)
ROUND(number)   -- rounds to nearest integer (0 decimal places)

Examples

SELECT
    ROUND(3.14159, 2) AS two_places,
    ROUND(3.14159, 0) AS zero_places,
    ROUND(3.14159)    AS no_arg,
    ROUND(3.5)        AS half_up,
    ROUND(2.5)        AS half_up2,
    ROUND(-3.7)       AS negative;

two_places	zero_places	no_arg	half_up	half_up2	negative
3.14	3	3	4	3	-4

warning

Banker's rounding: PostgreSQL and some other databases use "round half to even" (banker's rounding), where .5 rounds to the nearest even number. So ROUND(2.5) returns 2, not 3, and ROUND(3.5) returns 4.

MySQL and SQL Server use the traditional "round half up" rule where .5 always rounds up.

Expression	PostgreSQL	MySQL	SQL Server
ROUND(2.5)	2	3	3
ROUND(3.5)	4	4	4
ROUND(4.5)	4	5	5

This difference can cause discrepancies in financial calculations when migrating between databases.

Practical: round order totals to 2 decimal places:

SELECT
    order_id,
    subtotal,
    discount_pct,
    tax_rate,
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders;

order_id	subtotal	discount_pct	tax_rate	final_total
1001	155.97	10	0.0875	169.62
1002	299.99	0	0.0625	318.74
1003	45.48	5	0.0875	49.46
1004	89.99	15	0.1000	98.99
1005	9.99	0	0.0625	10.61

Clean, two-decimal results ready for display.

Rounding to Negative Decimal Places

You can round to the left of the decimal point by using negative values:

SELECT
    ROUND(1234.56, -1) AS tens,
    ROUND(1234.56, -2) AS hundreds,
    ROUND(1234.56, -3) AS thousands;

tens	hundreds	thousands
1230	1200	1000

Useful for rounding prices to the nearest $10, $100, etc., or for creating histogram buckets.

TRUNC / TRUNCATE: Rounding Without Rounding

TRUNC (PostgreSQL, Oracle) or TRUNCATE (MySQL) removes decimal digits without rounding. It simply chops off the unwanted digits.

-- PostgreSQL / Oracle
SELECT TRUNC(3.789, 1) AS truncated;
-- Result: 3.7 (not 3.8)

-- MySQL
SELECT TRUNCATE(3.789, 1) AS truncated;
-- Result: 3.7

-- Comparison
SELECT
    ROUND(3.789, 1) AS rounded,     -- 3.8
    TRUNC(3.789, 1) AS truncated;   -- 3.7

info

SQL Server does not have a TRUNC or TRUNCATE function for numbers. Use ROUND with a third argument of 1 to truncate:

-- SQL Server: ROUND with truncation flag
SELECT ROUND(3.789, 1, 1) AS truncated;
-- Result: 3.7

CEIL and FLOOR: Rounding to Integers

CEIL (or CEILING) rounds up to the nearest integer. FLOOR rounds down to the nearest integer.

Syntax

CEIL(number)     -- PostgreSQL, MySQL, Oracle
CEILING(number)  -- SQL Server (also supported by PostgreSQL and MySQL)
FLOOR(number)

Examples

SELECT
    CEIL(3.2)   AS ceil_positive,
    CEIL(3.8)   AS ceil_high,
    CEIL(-3.2)  AS ceil_negative,
    FLOOR(3.8)  AS floor_positive,
    FLOOR(3.2)  AS floor_low,
    FLOOR(-3.2) AS floor_negative;

ceil_positive	ceil_high	ceil_negative	floor_positive	floor_low	floor_negative
4	4	-3	3	3	-4

Notice how they behave with negative numbers:

• CEIL(-3.2) = -3 (rounds toward zero, which is "up" on the number line)
• FLOOR(-3.2) = -4 (rounds away from zero, which is "down" on the number line)

Practical Use Cases

Calculate number of shipping boxes needed:

-- Each box holds 25 items. How many boxes per product?
SELECT
    product_name,
    stock_qty,
    CEIL(stock_qty / 25.0) AS boxes_needed
FROM products
WHERE stock_qty > 0;

product_name	stock_qty	boxes_needed
Wireless Mouse	150	6
USB-C Cable	300	12
Notebook A5	500	20
Mechanical Keyboard	45	2
Pen Pack	800	32
Standing Desk	12	1

CEIL ensures partial boxes are rounded up. 45 items / 25 per box = 1.8, rounded up to 2 boxes.

Calculate pages needed for pagination:

-- 7 products, 3 per page
SELECT CEIL(COUNT(*) / 3.0) AS total_pages FROM products;
-- Result: 3 (pages 1-3, with the last page having only 1 item)

Price tiers using FLOOR:

-- Group products by $10 price tiers
SELECT
    product_name,
    price,
    FLOOR(price / 10) * 10 AS price_tier
FROM products
ORDER BY price_tier;

product_name	price	price_tier
Pen Pack	2.99	0
Notebook A5	4.50	0
USB-C Cable	9.99	0
Wireless Mouse	25.99	20
Desk Lamp	34.50	30
Mechanical Keyboard	89.99	80
Standing Desk	299.99	290

CEIL, FLOOR, ROUND Compared

SELECT
    value,
    FLOOR(value)   AS floor_val,
    CEIL(value)    AS ceil_val,
    ROUND(value)   AS round_val
FROM (VALUES (2.3), (2.5), (2.7), (-2.3), (-2.5), (-2.7))
    AS t(value);

value	floor_val	ceil_val	round_val
2.3	2	3	2
2.5	2	3	3*
2.7	2	3	3
-2.3	-3	-2	-2
-2.5	-3	-2	-3*
-2.7	-3	-2	-3

*Rounding behavior for .5 varies by database (see the ROUND section above).

ABS: Absolute Value

ABS returns the absolute (non-negative) value of a number, stripping any negative sign.

Syntax

ABS(number)

Examples

SELECT
    ABS(42)    AS positive,
    ABS(-42)   AS negative,
    ABS(0)     AS zero,
    ABS(-3.14) AS decimal_neg;

positive	negative	zero	decimal_neg
42	42	0	3.14

Practical: find products with price closest to a target:

-- Products closest to $30
SELECT
    product_name,
    price,
    ABS(price - 30) AS distance_from_30
FROM products
ORDER BY distance_from_30
LIMIT 3;

product_name	price	distance_from_30
Wireless Mouse	25.99	4.01
Desk Lamp	34.50	4.50
USB-C Cable	9.99	20.01

Calculate price variance from average:

SELECT
    product_name,
    price,
    ROUND(price - AVG(price) OVER(), 2) AS diff_from_avg,
    ROUND(ABS(price - AVG(price) OVER()), 2) AS abs_diff
FROM products;

product_name	price	diff_from_avg	abs_diff
Wireless Mouse	25.99	-40.86	40.86
USB-C Cable	9.99	-56.86	56.86
Notebook A5	4.50	-62.35	62.35
Mechanical Keyboard	89.99	23.14	23.14
Desk Lamp	34.50	-32.35	32.35
Pen Pack	2.99	-63.86	63.86
Standing Desk	299.99	233.14	233.14

MOD: Remainder (Modulo)

MOD returns the remainder after dividing one number by another.

Syntax

-- Function syntax (all databases)
MOD(dividend, divisor)

-- Operator syntax (PostgreSQL, MySQL, SQL Server, SQLite)
dividend % divisor

Examples

SELECT
    MOD(10, 3) AS mod_10_3,
    MOD(15, 5) AS mod_15_5,
    MOD(7, 2)  AS mod_7_2,
    MOD(10, 4) AS mod_10_4;

mod_10_3	mod_15_5	mod_7_2	mod_10_4
1	0	1	2

Check if a number is even or odd:

SELECT
    product_id,
    product_name,
    CASE WHEN MOD(product_id, 2) = 0 THEN 'Even' ELSE 'Odd' END AS parity
FROM products;

product_id	product_name	parity
101	Wireless Mouse	Odd
102	USB-C Cable	Even
103	Notebook A5	Odd
104	Mechanical Keyboard	Even
105	Desk Lamp	Odd
106	Pen Pack	Even
107	Standing Desk	Odd

Alternate row coloring for reports:

SELECT
    product_name,
    price,
    CASE WHEN MOD(ROW_NUMBER() OVER (ORDER BY product_name), 2) = 0
         THEN 'light' ELSE 'dark'
    END AS row_color
FROM products;

Distribute items across groups:

-- Distribute products into 3 groups
SELECT
    product_name,
    MOD(product_id, 3) AS group_number
FROM products;

product_name	group_number
Wireless Mouse	2
USB-C Cable	0
Notebook A5	1
Mechanical Keyboard	2
Desk Lamp	0
Pen Pack	1
Standing Desk	2

Check divisibility:

-- Find order IDs that are multiples of 2
SELECT order_id FROM orders WHERE MOD(order_id, 2) = 0;
-- Results: 1002, 1004

POWER and SQRT: Exponents and Roots

POWER raises a number to a specified exponent. SQRT returns the square root.

Syntax

POWER(base, exponent)
SQRT(number)

Examples

SELECT
    POWER(2, 3)   AS two_cubed,
    POWER(10, 2)  AS ten_squared,
    POWER(5, 0)   AS any_to_zero,
    POWER(2, 10)  AS two_to_ten,
    SQRT(144)     AS sqrt_144,
    SQRT(2)       AS sqrt_2;

two_cubed	ten_squared	any_to_zero	two_to_ten	sqrt_144	sqrt_2
8	100	1	1024	12	1.4142135623730951

Compound interest calculation:

-- Future value of $1000 at 5% annual interest over 10 years
SELECT
    1000 AS principal,
    0.05 AS rate,
    10 AS years,
    ROUND(1000 * POWER(1 + 0.05, 10), 2) AS future_value;

principal	rate	years	future_value
1000	0.05	10	1628.89

Geometric mean of prices:

-- Geometric mean using POWER and logarithms
SELECT
    ROUND(POWER(
        EXP(SUM(LN(price))),
        1.0 / COUNT(*)
    ), 2) AS geometric_mean
FROM products;

geometric_mean
22.85

Calculate distance between two points (Pythagorean theorem):

-- Distance between (3, 4) and (7, 1)
SELECT ROUND(SQRT(POWER(7 - 3, 2) + POWER(1 - 4, 2)), 2) AS distance;
-- Result: 5.00

SIGN: Determining Positive, Negative, or Zero

SIGN returns -1, 0, or 1 depending on whether the number is negative, zero, or positive.

Syntax

SIGN(number)

Examples

SELECT
    SIGN(42)   AS positive,
    SIGN(-42)  AS negative,
    SIGN(0)    AS zero;

positive	negative	zero
1	-1	0

Classify profit/loss:

SELECT
    product_name,
    price - cost AS profit,
    CASE SIGN(price - cost)
        WHEN 1  THEN 'Profit'
        WHEN 0  THEN 'Break Even'
        WHEN -1 THEN 'Loss'
    END AS classification
FROM products;

product_name	profit	classification
Wireless Mouse	13.49	Profit
USB-C Cable	6.24	Profit
Notebook A5	2.70	Profit
Mechanical Keyboard	47.99	Profit
Desk Lamp	16.25	Profit
Pen Pack	2.04	Profit
Standing Desk	144.99	Profit

LOG and EXP: Logarithmic Functions

These functions handle logarithmic and exponential calculations. They are used less frequently in everyday application queries but are essential for statistical analysis, financial modeling, and scientific computations.

Syntax

-- Natural logarithm (base e)
LN(number)       -- PostgreSQL, Oracle, MySQL
LOG(number)      -- SQL Server (natural log), MySQL (natural log)

-- Base-10 logarithm
LOG10(number)    -- all databases
LOG(10, number)  -- PostgreSQL, Oracle (general form)

-- Exponential (e raised to the power)
EXP(number)      -- all databases

Examples

SELECT
    LN(2.71828)   AS ln_e,
    LOG10(1000)   AS log10_thousand,
    EXP(1)        AS e_to_1,
    EXP(0)        AS e_to_0;

ln_e	log10_thousand	e_to_1	e_to_0
0.999999327347282	3	2.718281828459045	1

info

LOG function inconsistency: The meaning of LOG() varies across databases.

Database	LOG(x)	LN(x)	LOG10(x)
PostgreSQL	Base-10 log	Natural log	Same as LOG(x)
MySQL	Natural log	Natural log	Base-10 log
SQL Server	Natural log	Not available	Base-10 log
Oracle	Base-10 log	Natural log	Not available (use LOG(10, x))

To avoid confusion, use LN() for natural logarithm and LOG10() for base-10 logarithm when your database supports both.

Combining Numeric Functions: Practical Recipes

Full Order Calculation

Calculate discount, tax, and final total in one query:

SELECT
    order_id,
    subtotal,
    discount_pct,
    ROUND(subtotal * discount_pct / 100, 2) AS discount_amount,
    ROUND(subtotal * (1 - discount_pct / 100), 2) AS after_discount,
    ROUND(subtotal * (1 - discount_pct / 100) * tax_rate, 2) AS tax_amount,
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders;

order_id	subtotal	discount_pct	discount_amount	after_discount	tax_amount	final_total
1001	155.97	10.00	15.60	140.37	12.28	152.66
1002	299.99	0.00	0.00	299.99	18.75	318.74
1003	45.48	5.00	2.27	43.21	3.78	46.99
1004	89.99	15.00	13.50	76.49	7.65	84.14
1005	9.99	0.00	0.00	9.99	0.62	10.61

Markup and Margin Analysis

SELECT
    product_name,
    cost,
    price,
    ROUND((price - cost) / cost * 100, 1) AS markup_pct,
    ROUND((price - cost) / price * 100, 1) AS margin_pct,
    ROUND(price / cost, 2) AS price_to_cost_ratio
FROM products
ORDER BY margin_pct DESC;

product_name	cost	price	markup_pct	margin_pct	price_to_cost_ratio
Pen Pack	0.95	2.99	214.7	68.2	3.15
USB-C Cable	3.75	9.99	166.4	62.5	2.66
Notebook A5	1.80	4.50	150.0	60.0	2.50
Mechanical Keyboard	42.00	89.99	114.3	53.3	2.14
Wireless Mouse	12.50	25.99	107.9	51.9	2.08
Standing Desk	155.00	299.99	93.5	48.3	1.94
Desk Lamp	18.25	34.50	89.0	47.1	1.89

Percentage of Total

SELECT
    product_name,
    price * stock_qty AS inventory_value,
    ROUND(
        price * stock_qty / SUM(price * stock_qty) OVER() * 100,
        1
    ) AS pct_of_total
FROM products
ORDER BY inventory_value DESC;

product_name	inventory_value	pct_of_total
Mechanical Keyboard	4049.55	21.1
Wireless Mouse	3898.50	20.3
Standing Desk	3599.88	18.8
USB-C Cable	2997.00	15.6
Pen Pack	2392.00	12.5
Notebook A5	2250.00	11.7
Desk Lamp	0.00	0.0

Safe Percentage Calculations

-- Calculate discount percentage safely (handles zero subtotal)
SELECT
    order_id,
    subtotal,
    discount_pct,
    ROUND(
        COALESCE(
            discount_pct / NULLIF(subtotal, 0) * subtotal,
            0
        ), 2
    ) AS discount_amount
FROM orders;

order_id	subtotal	discount_pct	discount_amount
1001	155.97	10.00	10.00
1002	299.99	0.00	0.00
1003	45.48	5.00	5.00
1004	89.99	15.00	15.00
1005	9.99	0.00	0.00

Common Mistakes

Mistake 1: Integer Division Silently Truncating Results

-- PostgreSQL / SQL Server / SQLite
SELECT 1 / 3 AS result;
-- Result: 0 (not 0.333)

SELECT 5 / 2 AS result;
-- Result: 2 (not 2.5)

Fix: Make at least one operand a decimal:

SELECT 1.0 / 3 AS result;   -- 0.333...
SELECT 5 / 2.0 AS result;   -- 2.5

Mistake 2: Not Handling Division by Zero

-- Crashes the entire query
SELECT price / stock_qty FROM products;
-- ERROR: division by zero (when stock_qty = 0)

Fix:

SELECT price / NULLIF(stock_qty, 0) FROM products;
-- Returns NULL instead of crashing

Mistake 3: Rounding at the Wrong Stage

Rounding intermediate results instead of the final result introduces cumulative errors:

Wrong:

-- Rounding at each step loses precision
SELECT
    ROUND(subtotal * 0.10, 2) AS discount,  -- rounded
    ROUND(
        (subtotal - ROUND(subtotal * 0.10, 2)) * 0.0875,
        2
    ) AS tax  -- based on rounded discount
FROM orders WHERE order_id = 1001;

Correct:

-- Calculate with full precision, round only at the end
SELECT
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders WHERE order_id = 1001;

warning

Golden rule for financial calculations: Perform all arithmetic with full precision and round only the final result. Rounding intermediate values creates "penny errors" that compound across thousands of transactions.

Mistake 4: Using FLOAT for Money

-- FLOAT arithmetic is imprecise
SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT);
-- May return: 0.30000000000000004 instead of 0.3

-- DECIMAL arithmetic is exact
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));
-- Returns exactly: 0.30

Always use DECIMAL or NUMERIC for monetary values.

Mistake 5: Forgetting Operator Precedence

SQL follows standard mathematical precedence: multiplication and division before addition and subtraction.

-- Not what you might expect
SELECT 100 + 50 * 0.10;
-- Result: 105.0 (not 15.0)
-- Evaluates as: 100 + (50 * 0.10) = 100 + 5 = 105

-- Use parentheses for clarity
SELECT (100 + 50) * 0.10;
-- Result: 15.0

tip

Always use parentheses in complex expressions, even when operator precedence would give the correct result. Parentheses make your intent explicit and prevent bugs from misreading the expression.

-- Unclear: does the discount apply before or after tax?
SELECT subtotal - discount_pct / 100 * subtotal + tax_amount

-- Clear: discount applied first, then tax added
SELECT (subtotal * (1 - discount_pct / 100)) + tax_amount

Database Compatibility Reference

Function	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
ROUND(n, d)	Yes	Yes	Yes	Yes	Yes
CEIL() / CEILING()	Both	Both	CEILING()	CEIL()	No*
FLOOR()	Yes	Yes	Yes	Yes	No*
ABS()	Yes	Yes	Yes	Yes	Yes
MOD(a, b)	Yes	Yes	Use %	Yes	Use %
% operator	Yes	Yes	Yes	No	Yes
POWER(b, e)	Yes	Yes	Yes	Yes	Yes
SQRT()	Yes	Yes	Yes	Yes	No*
SIGN()	Yes	Yes	Yes	Yes	No
LN()	Yes	Yes	Use LOG()	Yes	No
LOG10()	Use LOG()	Yes	Yes	Use LOG(10,x)	No
EXP()	Yes	Yes	Yes	Yes	No
TRUNC(n, d)	Yes	TRUNCATE()	ROUND(n,d,1)	Yes	No
RANDOM()	RANDOM()	RAND()	RAND()	DBMS_RANDOM.VALUE	RANDOM()

*SQLite has limited built-in math functions but supports them via extensions.

Quick Reference

Task	Function	Example	Result
Round to N decimals	ROUND(n, d)	ROUND(3.456, 2)	3.46
Round up to integer	CEIL(n)	CEIL(3.2)	4
Round down to integer	FLOOR(n)	FLOOR(3.8)	3
Absolute value	ABS(n)	ABS(-42)	42
Remainder	MOD(a, b)	MOD(10, 3)	1
Exponent	POWER(b, e)	POWER(2, 10)	1024
Square root	SQRT(n)	SQRT(144)	12
Sign detection	SIGN(n)	SIGN(-5)	-1
Safe division	a / NULLIF(b, 0)	10 / NULLIF(0, 0)	NULL
Force decimal division	a / b::DECIMAL	7.0 / 2	3.5

Summary

SQL numeric functions provide a complete mathematical toolkit for working with numbers directly in your queries. From simple rounding to complex financial calculations, these functions let you process numeric data where it lives without round-tripping through application code.

Key takeaways:

• Arithmetic operators (+, -, *, /, %) work directly in SELECT, WHERE, and other clauses. Watch out for integer division in PostgreSQL, SQL Server, and SQLite, where dividing two integers truncates the result.
• ROUND is your most-used numeric function. Round only final results, not intermediate values, to avoid cumulative errors. Be aware of banker's rounding in PostgreSQL.
• CEIL rounds up, FLOOR rounds down. Use CEIL for "how many containers do I need?" calculations and FLOOR for bucket/tier grouping.
• ABS strips the sign from a number. Use it for distance calculations and variance analysis.
• MOD returns the remainder after division. Use it for even/odd checks, distributing items across groups, and cyclic patterns.
• POWER and SQRT handle exponentiation and roots. Essential for compound interest, distance calculations, and statistical formulas.
• Always protect against division by zero using NULLIF(divisor, 0).
• Use DECIMAL for money, never FLOAT. Floating-point arithmetic introduces rounding errors that compound in financial calculations.
• Use parentheses in complex expressions to make operator precedence explicit and prevent subtle bugs.

Master these functions and you can perform virtually any numeric calculation directly in SQL, keeping your data processing efficient and your application code clean.