ABS()
SQL · Math Functions
Theory
ABS returns the absolute value of a number, which is its distance from zero with the sign removed. ABS(-42) is 42, ABS(42) is 42 and ABS(0) is 0. It works on integers and decimals alike and returns NULL if the input is NULL.
In analytics, ABS is most useful when you care about the size of a difference but not its direction. If a sales target was ₹50,000 and actual sales were ₹47,500, then actual - target is -2,500, and ABS turns that into a 2,500 gap you can compare, sum or rank. It is also the building block of error metrics such as mean absolute error (AVG(ABS(actual - forecast))) and of tolerance checks like WHERE ABS(book_amount - bank_amount) <= 1 when reconciling two systems. ABS is spelled the same way in every major database, including SQLite.
Why we use it
Positive and negative differences cancel out when you add them, hiding real errors. ABS measures how far off something is regardless of direction, which is what most gap, error and tolerance checks need.
When to use it
Use ABS for variance size, forecast error, reconciliation tolerances and 'closest value' searches (ORDER BY ABS(x - target)). Keep the signed value when the direction matters, for example over- versus under-target.
How it works
- Read the input number
- Is the value NULL? Yes: Return NULL. No: Continue.
- Is the value negative? Yes: Flip the sign (-2500 becomes 2500). No: Keep it as it is.
- Return a non-negative number
Syntax
ABS(number)Example
SELECT ABS(-42);
-- 42Key points
- Removes the sign from negative numbers
- Useful for computing magnitude of differences
- Same spelling in every major database, including SQLite
- AVG(ABS(actual - forecast)) gives mean absolute error
- ORDER BY ABS(x - target) finds the value closest to a target