MySQL Math Functions#
ABS()#
Returns the absolute value of a number
SELECT ABS(-10), ABS(0), ABS(10);
#SELECT productName, productLine,msrp,ABS(ROUND(msrp - AVG(msrp) OVER(PARTITION BY productLine))) deviation FROM products ORDER BY productName;
SELECT productName, productLine,msrp,ABS(ROUND(msrp - AVG(msrp))) deviation FROM products GROUP BY productLine ORDER BY productName;
CEIL()#
Returns the smallest integer value greater than or equal to the input number (n).
SELECT CEIL(1.59);
SELECT CEIL(-1.59);
SELECT productLine, CEIL(AVG(msrp)) averageMsrp FROM products GROUP BY productLine ORDER BY averageMsrp;
FLOOR()#
Returns the largest integer value not greater than the argument
SELECT FLOOR(1.59);-- 1
SELECT FLOOR(-1.59);-- -2
SELECT productLine, FLOOR(AVG(quantityInStock)) averageStock FROM products GROUP BY productLine ORDER BY averageStock;
MOD()#
Returns the remainder of a number divided by another
SELECT MOD(11, 3);
SELECT 11 % 3;
SELECT MOD(10.5, 3);
SELECT orderNumber,SUM(quantityOrdered) Qty,IF(MOD(SUM(quantityOrdered),2),'Odd','Even') oddOrEven FROM orderdetails GROUP BY orderNumber ORDER BY orderNumber;
ROUND()#
Rounds a number to a specified number of decimal places.
SELECT ROUND(20.5);-- 21
SELECT ROUND(20.5, 0);-- 21
SELECT ROUND(121.55,-2);-- 100
SELECT ROUND(10.5);-- 11
SELECT ROUND(10.6);-- 11
SELECT ROUND(-10.5);-- -11
SELECT ROUND(-10.6);-- -11
SELECT ROUND(10.4);-- 10
SELECT ROUND(-10.4);-- -10
SELECT productCode,AVG(quantityOrdered * priceEach) avg_order_item_value FROM orderDetails GROUP BY productCode;
SELECT productCode,ROUND(AVG(quantityOrdered * priceEach)) avg_order_item_value FROM orderDetails GROUP BY productCode;
TRUNCATE()#
Truncates a number to a specified number of decimal places
SELECT TRUNCATE(1.555,1);
SELECT TRUNCATE(199.99,-2);
SELECT TRUNCATE(1.999,1), ROUND(1.999,1);
Others#
| Name | Description |
|---|---|
| ACOS(n) | Returns the arc cosine of n or null if n is not in the range -1 and 1. |
| ASIN(n) | Returns the arc sine of n which is the value whose sine is n. It returns null if n is not in the range -1 to 1. |
| ATAN() | Returns the arc tangent of n. |
| ATAN2(n,m), ATAN(m,n) | Returns the arc tangent of the two variables n and m |
| CONV(n,from_base,to_base) | Converts a number between different number bases |
| COS(n) | Returns the cosine of n, where n is in radians |
| COT(n) | Returns the cotangent of n. |
| CRC32() | Computes a cyclic redundancy check value and returns a 32-bit unsigned value |
| DEGREES(n) | Converts radians to degrees of the argument n |
| EXP(n) | Raises to the power of e raised to power of n |
| LN(n) | Returns the natural logarithm of n |
| LOG(n) | Returns the natural logarithm of the first argument |
| LOG10() | Returns the base-10 logarithm of the argument |
| LOG2() | Returns the base-2 logarithm of the argument |
| PI() | Returns the value of PI |
| POW() | Returns the argument raised to the specified power |
| POWER() | Returns the argument raised to the specified power |
| RADIANS() | Returns argument converted to radians |
| RAND() | Returns a random floating-point value |
| SIGN(n) | Returns the sign of n that can be -1, 0, or 1 depending on whether n is negative, zero, or positive. |
| SIN(n) | Returns the sine of n |
| SQRT(n) | Returns the square root of n |
| TAN(n) | Returns the tangent of n |