{"id":4007,"date":"2025-06-07T09:37:33","date_gmt":"2025-06-07T09:37:33","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4007"},"modified":"2025-06-07T09:37:34","modified_gmt":"2025-06-07T09:37:34","slug":"sql-math-functions","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-math-functions\/","title":{"rendered":"SQL Math Functions"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL provides built-in math functions to perform calculations directly in queries. These functions help with numeric analysis, transformations, and conditional logic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Common SQL Math Functions with Examples<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- Absolute value\nSELECT ABS(-42);                   -- 42\n\n-- Rounding numbers\nSELECT ROUND(123.456, 2);          -- 123.46\nSELECT CEIL(123.1);                -- 124 (MySQL\/PostgreSQL)\nSELECT CEILING(123.1);             -- 124 (SQL Server)\nSELECT FLOOR(123.9);               -- 123\n\n-- Square root and power\nSELECT SQRT(49);                   -- 7\nSELECT POWER(2, 3);                -- 8\n\n-- Modulus (remainder)\nSELECT MOD(10, 3);                 -- 1\nSELECT 10 % 3;                     -- 1 (PostgreSQL, SQL Server)\n\n-- Random numbers\nSELECT RAND();                     -- Random float between 0 and 1 (MySQL, SQL Server)\nSELECT RANDOM();                   -- PostgreSQL\n\n-- Trigonometric (if supported)\nSELECT SIN(PI()\/2);                -- 1\nSELECT COS(0);                     -- 1\n\n-- PI constant\nSELECT PI();                       -- 3.141592...<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Real-World Examples<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- Get rounded discount percentage\nSELECT ROUND((discount_price \/ original_price) * 100, 2) AS discount_pct\nFROM products;\n\n-- Check if number is even\nSELECT CASE WHEN MOD(id, 2) = 0 THEN 'Even' ELSE 'Odd' END FROM users;\n\n-- Generate a random OTP-like number (MySQL)\nSELECT FLOOR(100000 + RAND() * 900000) AS otp;\n<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Function Availability by DBMS<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Function<\/th><th>MySQL<\/th><th>PostgreSQL<\/th><th>SQL Server<\/th><\/tr><\/thead><tbody><tr><td><code>ABS()<\/code><\/td><td>\u2705<\/td><td>\u2705<\/td><td>\u2705<\/td><\/tr><tr><td><code>ROUND()<\/code><\/td><td>\u2705<\/td><td>\u2705<\/td><td>\u2705<\/td><\/tr><tr><td><code>CEIL()<\/code><\/td><td>\u2705<\/td><td>\u2705<\/td><td><code>CEILING()<\/code><\/td><\/tr><tr><td><code>FLOOR()<\/code><\/td><td>\u2705<\/td><td>\u2705<\/td><td>\u2705<\/td><\/tr><tr><td><code>MOD()<\/code> \/ <code>%<\/code><\/td><td>\u2705 \/ \u274c<\/td><td>\u2705 \/ \u2705<\/td><td>\u2705 \/ \u2705<\/td><\/tr><tr><td><code>RAND()<\/code><\/td><td>\u2705<\/td><td>\u274c (<code>RANDOM()<\/code>)<\/td><td>\u2705<\/td><\/tr><tr><td><code>SQRT()<\/code> \/ <code>POWER()<\/code><\/td><td>\u2705<\/td><td>\u2705<\/td><td>\u2705<\/td><\/tr><tr><td><code>PI()<\/code> \/ <code>SIN()<\/code> etc.<\/td><td>\u2705<\/td><td>\u2705<\/td><td>\u2705<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83e\udde0 <strong>Quick Recap<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Task<\/th><th>Example<\/th><\/tr><\/thead><tbody><tr><td>Absolute Value<\/td><td><code>ABS(-10)<\/code><\/td><\/tr><tr><td>Rounding<\/td><td><code>ROUND(12.345, 2)<\/code><\/td><\/tr><tr><td>Ceiling \/ Floor<\/td><td><code>CEIL(3.2)<\/code>, <code>FLOOR(3.9)<\/code><\/td><\/tr><tr><td>Power \/ Root<\/td><td><code>POWER(2,3)<\/code>, <code>SQRT(25)<\/code><\/td><\/tr><tr><td>Modulus<\/td><td><code>MOD(7,2)<\/code><\/td><\/tr><tr><td>Random Value<\/td><td><code>RAND()<\/code> \/ <code>RANDOM()<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Math functions are powerful tools to prepare numerical data for reports, validations, dashboards, and logic branching.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL provides built-in math functions to perform calculations directly in queries. These functions help with numeric analysis, transformations, and conditional logic. \ud83d\udd39 Common SQL Math Functions with Examples \ud83d\udd39 Real-World Examples \ud83d\udd39 Function Availability by DBMS Function MySQL PostgreSQL SQL Server ABS() \u2705 \u2705 \u2705 ROUND() \u2705 \u2705 \u2705 CEIL() \u2705 \u2705 CEILING() FLOOR() \u2705 [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-4007","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}