Numeric Functions

ROUND, ABS and the modulo operator in MySQL 8.4, plus why 5/2 is 2.5000 and how to get integer division instead.

SyntaxROUND(X [, D]) | ABS(X) | MOD(N, M) = N % M = N MOD M | N DIV M

Arithmetic in SQL is mostly plain operators, but rounding and division have rules worth learning before they surprise you in a financial report.

Rounding

ROUND(X) rounds to zero decimal places; ROUND(X, D) rounds to D places, and a negative D zeroes digits to the left of the point — ROUND(23.298, -1) is 20. The tie-breaking rule depends on the type: exact-value numbers (DECIMAL, integers) round half away from zero, while approximate-value numbers (FLOAT, DOUBLE) round half to even on most systems. ROUND(2.5) is 3 but ROUND(25E-1) is 2. TRUNCATE(X, D) always cuts toward zero and requires D. ABS(X) returns the magnitude.

Division

/ never returns an integer. With two exact-value operands, the result scale is the scale of the first operand plus div_precision_increment (4 by default), so 5/2 is 2.5000. For integer division use DIV, which discards the fractional part toward zero: -5 DIV 2 is -2, not -3.

Modulo

MOD(N, M), N % M and N MOD M are three spellings of one operation. MOD(N, 0) returns NULL, as does any division by zero in a SELECT. In an INSERT or UPDATE it is an error under the MySQL 8.4 default sql_mode, which enables both STRICT_TRANS_TABLES and ERROR_FOR_DIVISION_BY_ZERO.

Example

Example · sql
-- Order summary: money rounded once, at the end, on DECIMAL columns
SELECT
  o.id,
  SUM(l.unit_price * l.qty)                                   AS net,
  -- unit_price is DECIMAL(10,2), so rounding is half away from zero
  ROUND(SUM(l.unit_price * l.qty) * 0.20, 2)                  AS vat,
  ROUND(SUM(l.unit_price * l.qty) * 1.20, 2)                  AS gross,
  -- / always returns a fraction: scale is the first operand's scale + 4
  ROUND(SUM(l.unit_price * l.qty) / SUM(l.qty), 2)            AS avg_unit_price,
  -- Whole boxes and leftover units: DIV truncates, MOD gives the remainder
  SUM(l.qty) DIV 12                                           AS full_boxes,
  SUM(l.qty) MOD 12                                           AS loose_units,
  -- How far this order sits from the customer's usual basket size
  ABS(SUM(l.unit_price * l.qty) - c.avg_order_value)          AS deviation
FROM orders o
JOIN order_lines l ON l.order_id = o.id
JOIN customers   c ON c.id = o.customer_id
GROUP BY o.id, c.avg_order_value;

When to use it

  • Round a computed tax or discount to two decimal places before it is written to an invoice
  • Split a queue of rows across N workers with id MOD N
  • Convert a total quantity into whole cases plus a remainder using DIV and MOD
  • Flag reconciliation mismatches larger than a tolerance with ABS

More examples

Exact versus approximate rounding

The same literal rounds differently depending on whether MySQL sees it as exact or approximate. This is the concrete reason to declare currency as DECIMAL: a DOUBLE column will round half-way values to even and will not reproduce the totals your accounting system expects.

Example · sql
SELECT
  ROUND(2.5)    AS decimal_half,   -- 3: exact value, half away from zero
  ROUND(25E-1)  AS double_half,    -- 2: approximate value, half to even
  ROUND(1.298, 1)  AS one_place,   -- 1.3
  ROUND(23.298, -1) AS tens,       -- 20
  TRUNCATE(1.999, 1) AS cut;       -- 1.9, never rounds up

-- Which is why money columns are declared exact:
CREATE TABLE invoices (
  id     BIGINT UNSIGNED PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL   -- not FLOAT or DOUBLE
);

Bucket rows without a window function

MOD spreads rows evenly across a fixed number of consumers and DIV converts a zero-based position into a page number. Both are useful when you need a cheap, repeatable partitioning rule and do not want the cost of a window function or a coordinator table.

Example · sql
-- Split a backlog across 4 workers deterministically by id
SELECT id, payload
FROM jobs
WHERE status = 'pending'
  AND id MOD 4 = 2       -- worker 2 of 0..3; % 4 and MOD(id,4) are identical
ORDER BY id
LIMIT 100;

-- Which page a row falls on, 50 rows per page
SELECT id, (row_index DIV 50) + 1 AS page_number
FROM ranked_rows;

Tolerance comparison with ABS

ABS turns a two-sided comparison into one condition, so you do not have to write diff > 0.01 OR diff < -0.01. Keep the signed difference in the SELECT list as well — you almost always want to know which side is short.

Example · sql
-- Reconciliation: flag ledger rows that disagree with the bank by more than 1p
SELECT l.id, l.amount, b.amount AS bank_amount,
       l.amount - b.amount        AS signed_diff,
       ABS(l.amount - b.amount)   AS gap
FROM ledger l
JOIN bank_lines b ON b.reference = l.reference
WHERE ABS(l.amount - b.amount) > 0.01
ORDER BY gap DESC;

Related

sql string functionssql castsql coalescesql printfsql sum avg

Discussion

  • Be the first to comment on this lesson.