SQL - Numeric Functions
SQL - Numeric Functions
Section titled “SQL - Numeric Functions”SQL includes a rich set of built-in functions for performing operations on numeric data. These functions can be broadly categorized and are essential for data analysis, transformation, and calculation directly within your queries. The exact function names and capabilities can vary slightly between SQL dialects like PostgreSQL, MySQL, and SQL Server, but the core concepts are universal.
Scalar Numeric Functions
Section titled “Scalar Numeric Functions”These functions operate on a single numeric value and return a single value. Below is a list of common functions with examples.
| Function | Description | Example Usage & Result |
|---|---|---|
| ABS(x) | Returns the absolute (non-negative) value of x. | SELECT ABS(-15.7); → 15.7 |
| ROUND(x, d) | Rounds the value x to d decimal places. If d is omitted, rounds to the nearest integer. | SELECT ROUND(123.456, 2); → 123.46 |
| CEIL() / CEILING(x) | Returns the smallest integer value that is greater than or equal to x. | SELECT CEILING(15.2); → 16 |
| FLOOR(x) | Returns the largest integer value that is less than or equal to x. | SELECT FLOOR(15.7); → 15 |
| POWER(x, y) | Returns the value of x raised to the power of y. | SELECT POWER(3, 4); → 81 |
| SQRT(x) | Returns the non-negative square root of x. | SELECT SQRT(64); → 8 |
| MOD(x, y) | Returns the remainder of x divided by y (modulo operator). | SELECT MOD(10, 3); → 1 |
| SIGN(x) | Returns -1, 0, or 1 depending on whether x is negative, zero, or positive. | SELECT SIGN(-25); → -1 |
| PI() | Returns the constant value of Pi. | SELECT PI(); → 3.14159265... |
| RAND() / RANDOM() | Returns a random floating-point value between 0 and 1. (Syntax varies by RDBMS). | SELECT RAND(); → 0.12345... (a random value) |
Trigonometric and Logarithmic Functions
Section titled “Trigonometric and Logarithmic Functions”These are more specialized mathematical functions used in scientific and engineering calculations. Most systems expect angles to be in radians.
| Function | Description |
|---|---|
| SIN(x), COS(x), TAN(x) | Return the sine, cosine, and tangent of x, where x is an angle in radians. |
| ASIN(x), ACOS(x), ATAN(x) | Return the arc sine, arc cosine, and arc tangent of x. |
| DEGREES(x), RADIANS(x) | Convert a value from radians to degrees and from degrees to radians, respectively. |
| LOG(x), LOG10(x), LN(x) | Return the natural logarithm (base e) or base-10 logarithm of x. |
| EXP(x) | Returns e raised to the power of x. |
Note on Aggregate Functions: Do not confuse scalar numeric functions with aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX(). Aggregate functions operate on a set of values (e.g., all values in a column) to return a single summary value, often used with the GROUP BY clause.