What are numeric and mathematical functions in MySQL?

Question

Accepted Answer

31
   What are numeric and mathematical functions in MySQL?
  Level: Intermediate — Used for calculations, rounding, and formatting numeric output in reports.

  Numeric functions perform mathematical operations on numeric values and return a number. They are essential for financial calculations, statistics, and formatting results.

   Common Numeric Functions
  
     ROUND(): Rounds a number to a specified number of decimal places.
     CEIL() / CEILING(): Rounds up to the nearest integer.
     FLOOR(): Rounds down to the nearest integer.
     TRUNCATE(): Cuts a number to a set of decimals without rounding.
     MOD(): Returns the remainder of a division (same as %).
     ABS(): Returns the absolute (non-negative) value.
     POWER() / SQRT(): Raises to a power / returns the square root.
     RAND(): Returns a random float between 0 and 1.
  

  
     ROUND vs TRUNCATE gotcha: ROUND(3.567, 2) gives 3.57 (rounds), while TRUNCATE(3.567, 2) gives 3.56 (simply chops off). Don't confuse the two in financial calculations.
  

   Quick Reference with Examples
  
    
      
        
          Function
          Example
          Result
        
      
      
        
          ROUND()
          ROUND(3.567, 2)
          3.57
        
        
          CEIL()
          CEIL(4.1)
          5
        
        
          FLOOR()
          FLOOR(4.9)
          4
        
        
          TRUNCATE()
          TRUNCATE(3.567, 2)
          3.56
        
        
          MOD()
          MOD(10, 3)
          1
        
        
          ABS()
          ABS(-25)
          25
        
        
          POWER()
          POWER(2, 3)
          8
        
        
          SQRT()
          SQRT(16)
          4
        
      
    
  

   Quick Example
-- Round prices to 2 decimal places
SELECT product, ROUND(price, 2) AS price_rounded FROM products;

-- Find even vs odd IDs using MOD
SELECT id, IF(MOD(id, 2) = 0, 'Even', 'Odd') AS parity FROM users;

-- Ceiling and floor for pagination math
SELECT CEIL(105 / 10) AS total_pages;   -- 11 pages for 105 rows

-- Random sample of 5 employees
SELECT * FROM employees ORDER BY RAND() LIMIT 5;

-- Compound calculation
SELECT POWER(1.05, 10) AS growth_factor;  -- 5% growth over 10 periods

  
     Interviewer tip: Group them — rounding (ROUND/CEIL/FLOOR/TRUNCATE), arithmetic (MOD/ABS/POWER/SQRT), and random (RAND). Always highlight the ROUND vs TRUNCATE difference.

Function	Example	Result
ROUND()	`ROUND(3.567, 2)`	3.57
CEIL()	`CEIL(4.1)`	5
FLOOR()	`FLOOR(4.9)`	4
TRUNCATE()	`TRUNCATE(3.567, 2)`	3.56
MOD()	`MOD(10, 3)`	1
ABS()	`ABS(-25)`	25
POWER()	`POWER(2, 3)`	8
SQRT()	`SQRT(16)`	4

What are numeric and mathematical functions in MySQL?

Answer with Explanation

What are numeric and mathematical functions in MySQL?

Common Numeric Functions

Quick Reference with Examples

Quick Example