Jayen Thakker 2025
Data Analytics Mentor
Data Analytics
Mathe Functions in SQL every
data analyst should know
Mathematical Functions in SQL Swipe next
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
ABS()
Purpose:
Returns the absolute (non-negative) value of a number.
Example:
SELECT ABS(-42) AS abs_value;
Use-case:
To clean negative values where only magnitudes matter (e.g.
distances, deviations).
Mathematical Functions in SQL 02
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
ROUND()
Purpose:
Rounds a number to a specified number of decimal places.
Syntax:
ROUND(number, decimal_places)
Example:
SELECT ROUND(123.4567, 2) AS rounded_value;
Use-case:
Reporting financial figures rounded to 2 decimal places
Simplifying numeric outputs for dashboards.
Mathematical Functions in SQL 03
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
CEIL() / CEILING()
Purpose:
Rounds a number up to the nearest integer.
Example:
SELECT CEIL(4.2) AS ceil_value;
Use-case:
Calculating required number of pages, bins, or packages
when partial units cannot exist.
Mathematical Functions in SQL 04
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
FLOOR()
Purpose:
Rounds a number down to the nearest integer.
Example:
SELECT FLOOR(4.9) AS floor_value;
Use-case:
Grouping into integer ranges, e.g. age groups, price brackets.
Mathematical Functions in SQL 05
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
POWER()
Purpose:
Raises a number to a specified power.
Syntax:
POWER(base, exponent)
Example:
SELECT POWER(3, 4) AS power_value;
Use-case:
Financial modeling (e.g. compound interest)
Scientific calculations.
Mathematical Functions in SQL 06
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
SQRT()
Purpose:
Returns the square root of a number.
Example:
SELECT SQRT(25) AS sqrt_value;
Use-case:
Statistical measures like standard deviation
Distance calculations in geometry.
Mathematical Functions in SQL 07
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
EXP()
Purpose:
Returns e (Euler’s number, ~2.718) raised to the power of a
specified number.
Example:
SELECT EXP(2) AS exp_value;
Use-case:
Growth models
Logarithmic scale transformations.
Mathematical Functions in SQL 08
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
LN() / LOG()
Purpose:
Returns the natural logarithm (base e) of a number.
Example:
SELECT LN(7.389056) AS ln_value;
Note:
In some databases, LOG() with one argument is the same as
LN().
Use-case:
Statistical transformations
Data normalization for machine learning.
Mathematical Functions in SQL 09
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
LOG10()
Purpose:
Returns the base-10 logarithm of a number.
Example:
SELECT LOG10(1000) AS log10_value;
Use-case:
Transforming skewed data
Calculating orders of magnitude.
Mathematical Functions in SQL 10
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
MOD() or % Operator
Purpose:
Returns the remainder of division (modulus).
Syntax:
MOD(number, divisor)
Example:
SELECT MOD(10, 3) AS mod_value;
Use-case:
Checking even/odd numbers
Creating cyclic groups (e.g. shift scheduling).
Mathematical Functions in SQL 11
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
SIGN()
Purpose:
Returns the sign of a number
-1 if negativ
0 if zer
1 if positive
Example:
SELECT SIGN(-15) AS sign_value;
Use-case:
Flagging positive vs negative trends
Applying conditional calculations.
Mathematical Functions in SQL 12
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
PI()
Purpose:
Returns the value of π (pi).
Example:
SELECT PI() AS pi_value;
Use-case:
Geometry calculations
Trigonometry in spatial analysis.
Mathematical Functions in SQL 13
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
TRUNC()
Purpose:
Truncates a number to a specified number of decimal
places without rounding.
Example (in Oracle/PostgreSQL):
SELECT TRUNC(123.4567, 2) AS truncated_value;
Use-case:
Dropping insignificant decimals for consistent reporting.
Mathematical Functions in SQL 14
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
RAND() or RANDOM()
Purpose:
Returns a random floating-point number between 0 and 1.
Example (PostgreSQL):
SELECT RANDOM() AS rand_value;
Example (MySQL):
SELECT RAND() AS rand_value;
Use-case:
Sampling data
Randomizing test records.
Mathematical Functions in SQL 15
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
GREATEST() and LEAST()
Purpose:
GREATEST(): Returns the largest value among multiple
expressions
LEAST(): Returns the smallest value among multiple
expressions.
Example:
SELECT GREATEST(10, 25, 3) AS max_value,
LEAST(10, 25, 3) AS min_value;
Use-case:
Setting upper/lower bounds in calculations
Data validation rules.
Mathematical Functions in SQL 16
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
SUM(), AVG(), MIN(), MAX(),
COUNT()
Though technically aggregate functions, they’re fundamental
math tools for any analyst.
Example:
SELECT
SUM(sales) AS total_sales,
AVG(sales) AS avg_sales,
MIN(sales) AS min_sale,
MAX(sales) AS max_sale,
COUNT(*) AS num_records
FROM sales_data;
Use-case:
Core statistical summaries
Reporting KPIs.
Mathematical Functions in SQL 17
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
VARIANCE() / VAR_SAMP(),
STDDEV() / STDDEV_SAMP()
Purpose:
Measures data spread (variance or standard deviation).
Example (PostgreSQL):
SELECT
VARIANCE(sales) AS var_sales,
STDDEV(sales) AS std_sales
FROM sales_data;
Use-case:
Understanding variability in business metrics
Building statistical models.
Mathematical Functions in SQL 18
TM
Start Your Data
Analytics Journey
Today!
Learn. Practice. Get Mentored.
with [Link]
If you're interested in learning
data analytics in 90 Days.
DM “Course” to
join the course.
