Mathematical Functions

By the end of this lesson, you will be able to apply standard mathematical functions within SQL queries to transform numeric data for analysis and reporting.

What it is

Mathematical functions in SQL are built-in operations that perform calculations on numeric columns or expressions. Unlike programming languages where you might write custom loops for math, SQL provides declarative functions like ROUND(), ABS(), SQRT(), and POWER(). These functions operate row-by-row, allowing you to clean data, calculate metrics (like growth rates), or format output directly within your query logic. Related terms include "scalar functions" (functions that return a single value per row) and "expressions" (combinations of values, operators, and functions).

Why it matters

• Data Cleaning: Use ROUND() to remove floating-point noise from financial reports or scientific measurements.
• Metric Calculation: Compute complex KPIs such as percentage change using ABS() and division without exporting raw data to another tool.
• Performance Optimization: Performing calculations in the database engine is often faster than moving large datasets to an application layer for processing.
• Standardization: Ensure consistent formatting across different reports by applying uniform rounding rules at the source.

Syntax or steps

The general syntax for most mathematical functions follows the pattern: FUNCTION_NAME(argument). Some functions accept multiple arguments, such as ROUND(value, decimals). You can nest these functions inside SELECT statements or use them in WHERE clauses for filtering based on calculated results.

Example

SELECT 
    product_name,
    original_price,
    ROUND(original_price * 0.9, 2) AS discounted_price,
    ABS(original_price - 100) AS price_difference_from_100
FROM products
WHERE original_price > 50;

This query selects product names and prices. It calculates a 10% discount rounded to two decimal places and determines the absolute difference between the current price and a benchmark of 100. The WHERE clause filters out low-cost items before calculation.

Common mistakes

• Integer Division: In some SQL dialects, dividing two integers truncates the result (e.g., 5 / 2 = 2). Fix this by casting one operand to a float or decimal type, e.g., CAST(5 AS FLOAT) / 2.
• NULL Propagation: If any argument in a mathematical function is NULL, the result is usually NULL. Use COALESCE(column, 0) to handle missing values before calculating.
• Incorrect Rounding Precision: Forgetting to specify the number of decimal places in ROUND() may default to zero decimals, losing significant precision in financial data.
• Negative Square Roots: Attempting SQRT(-4) will cause an error in most databases. Always validate input ranges or use ABS() if appropriate for the context.

When to use it

Use SQL mathematical functions when the calculation is simple, row-level, and needed for immediate reporting or filtering. Use application-layer code (Python, Java, etc.) when calculations involve complex logic, external APIs, or iterative processes not supported by SQL.

Practice

Guided Exercise: Write a query that selects employee_id and calculates their annual salary bonus as 15% of their monthly_salary multiplied by 12, rounded to the nearest whole number.

Challenge: Modify the previous query to only show employees whose calculated bonus exceeds 5000. Hint: You cannot reference the alias bonus directly in the WHERE clause; repeat the expression or use a subquery/CTE.

Quick check

Question: What happens if you try to divide by zero in a standard SQL query?

Answer: Most SQL databases will throw an error or return NULL, depending on the specific system configuration and dialect. It is best practice to filter out zero denominators using WHERE denominator != 0.

Summary

SQL mathematical functions provide efficient, declarative ways to manipulate numeric data directly within the database. Mastering functions like ROUND(), ABS(), and handling edge cases like NULLs and integer division ensures accurate and performant analytics pipelines.