Math Functions
18 Functions
Math functions allow you to perform numerical calculations directly within your SQL queries. Whether you are calculating financial discounts, determining leaderboard rankings, or generating random rewards, these tools help you process numbers efficiently.
Table of Contents
ABS()
Returns the absolute (positive) value of a number.
ABS(x)Calculating the difference between two scores in a match, regardless of who scored higher.
SELECT ABS(score_a - score_b) AS point_gap FROM matches;This is perfect for distance calculations or any scenario where you only care about the magnitude of a change, not the direction.
ROUND()
Rounds a number to the nearest integer or a specified number of decimal places.
ROUND(number, decimal_places)Displaying a clean price after a 15% tax calculation.
SELECT ROUND(price * 1.15, 2) FROM products;If decimal_places is omitted, it rounds to the nearest whole number.
CEIL() / CEILING()
Returns the smallest integer greater than or equal to a number (always rounds up).
CEIL(x)Calculating how many pages are needed for pagination if you have 11 items and 5 items per page.
SELECT CEIL(11.0 / 5.0); -- Result: 3Think of this as "the ceiling"βit always pushes the number to the next highest integer, no matter how small the decimal.
FLOOR()
Returns the largest integer less than or equal to a number (always rounds down).
FLOOR(x)Determining how many full levels a user has earned based on total XP.
SELECT FLOOR(total_xp / 1000) AS level FROM users;Use FLOOR when you need to discard fractional parts without rounding up.
TRUNC()
Truncates a number to a specified number of decimal places by removing extra digits.
TRUNC(number, precision)Showing a raw price without rounding (e.g., 9.999 becomes 9.99).
SELECT TRUNC(9.999, 2); -- Result: 9.99Unlike ROUND, TRUNC never changes the value of the digits; it simply "cuts off" the end.
POWER()
Raises a number to the power of another (x^y).
POWER(base, exponent)Calculating compound interest or exponential difficulty scaling in a game.
SELECT POWER(2, 3); -- Result: 8You can use fractions as the exponent for roots, like POWER(9, 0.5) for a square root.
SQRT()
Returns the square root of a non-negative number.
SQRT(x)Part of the formula for calculating the geometric distance between two points.
SELECT SQRT(16); -- Result: 4Trying to use SQRT on a negative number will cause an error. Use ABS first if needed!
CBRT()
Returns the cube root of a number.
CBRT(x)Calculating dimensions based on volume.
SELECT CBRT(27); -- Result: 3Useful for 3D physics calculations or volumetric data.
EXP()
Returns the exponential value (e^x).
EXP(x)Used in complex statistical models or natural growth calculations.
SELECT EXP(1.0); -- Result: 2.718...Often the inverse operation of LN().
LN() / LOG()
LN returns the natural logarithm, while LOG returns the base-10 logarithm.
LN(x) or LOG(x)Calculating the magnitude of a value on a logarithmic scale.
SELECT LOG(100); -- Result: 2Logarithms are great for normalizing data with massive variance (e.g., income distribution).
MOD()
Returns the remainder of a division.
MOD(dividend, divisor) or dividend % divisorChecking if a number is even or odd (e.g., MOD(id, 2) = 0).
SELECT MOD(10, 3); -- Result: 1This is essential for features like alternating row colors or cycling through items in a list.
DIV()
Performs integer division (returns the quotient without the remainder).
DIV(y, x)Calculating how many "stacks" of items a player has if each stack holds 64.
SELECT DIV(130, 64); -- Result: 2Returns the integer part of the division result.
SIGN()
Returns -1 for negative numbers, 0 for zero, and 1 for positive numbers.
SIGN(x)Determining the direction of a change (up, down, or flat).
SELECT SIGN(new_xp - old_xp);Useful for visualizing trends (red for -1, green for 1).
RANDOM()
Returns a random float between 0.0 and 1.0.
RANDOM()Selecting a random winner for a giveaway or a random challenge in the Arena.
SELECT * FROM questions ORDER BY RANDOM() LIMIT 1;To get a random integer between 1 and 10, use: FLOOR(RANDOM() * 10 + 1).
PI()
Returns the constant value of Ο β 3.14159.
PI()Calculating the area of a circular zone or boundary.
SELECT PI();More precise than manually typing 3.14159...
DEGREES()
Converts radians to degrees.
DEGREES(radians)Converting scientific coordinate data into human-readable map angles.
SELECT DEGREES(PI()); -- Result: 180Scientific functions usually output radians, so this is needed for UI display.
RADIANS()
Converts degrees to radians.
RADIANS(degrees)Preparing degree values for use in trigonometric functions like SIN or COS.
SELECT RADIANS(180); -- Result: 3.14159...SQL trig functions (SIN, COS, TAN) expect input in radians, not degrees.
WIDTH_BUCKET()
Assigns a value to a "bucket" (group) within a specified range.
WIDTH_BUCKET(value, min, max, count)Automatically grouping users into 4 "XP Tiers" based on their total score.
SELECT username, WIDTH_BUCKET(xp, 0, 5000, 4) FROM users;This is a powerful "advanced" math function that makes building histograms or tiered ranking systems incredibly easy.