SQL Math Functions

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

SQL provides built-in math functions to perform calculations directly in queries. These functions help with numeric analysis, transformations, and conditional logic.

🔹 Common SQL Math Functions with Examples

-- Absolute value
SELECT ABS(-42);                   -- 42

-- Rounding numbers
SELECT ROUND(123.456, 2);          -- 123.46
SELECT CEIL(123.1);                -- 124 (MySQL/PostgreSQL)
SELECT CEILING(123.1);             -- 124 (SQL Server)
SELECT FLOOR(123.9);               -- 123

-- Square root and power
SELECT SQRT(49);                   -- 7
SELECT POWER(2, 3);                -- 8

-- Modulus (remainder)
SELECT MOD(10, 3);                 -- 1
SELECT 10 % 3;                     -- 1 (PostgreSQL, SQL Server)

-- Random numbers
SELECT RAND();                     -- Random float between 0 and 1 (MySQL, SQL Server)
SELECT RANDOM();                   -- PostgreSQL

-- Trigonometric (if supported)
SELECT SIN(PI()/2);                -- 1
SELECT COS(0);                     -- 1

-- PI constant
SELECT PI();                       -- 3.141592...

🔹 Real-World Examples

-- Get rounded discount percentage
SELECT ROUND((discount_price / original_price) * 100, 2) AS discount_pct
FROM products;

-- Check if number is even
SELECT CASE WHEN MOD(id, 2) = 0 THEN 'Even' ELSE 'Odd' END FROM users;

-- Generate a random OTP-like number (MySQL)
SELECT FLOOR(100000 + RAND() * 900000) AS otp;

🔹 Function Availability by DBMS

🧠 Quick Recap

💡 Math functions are powerful tools to prepare numerical data for reports, validations, dashboards, and logic branching.