SQL Numeric Functions
Reports are full of arithmetic: rounding averages, working out percentages, splitting items into boxes, finding even ids. MySQL’s numeric functions handle all of it in the query. This lesson covers the ones you will use weekly and the integer-division trap that catches many people.
- Round with
ROUND,TRUNCATE,CEILandFLOOR - Use
MOD,DIV,ABS,POWERandSQRT - Calculate percentages without rounding errors
- Know how
/behaves in MySQL
Quick reference
SELECT ROUND(1234.567, 2) AS round_2, ROUND(1234.567, -2) AS round_hundreds, TRUNCATE(1234.567, 1) AS truncate_1, CEIL(4.1) AS ceil_, FLOOR(4.9) AS floor_, ABS(-15) AS abs_, MOD(17, 5) AS mod_, 17 DIV 5 AS int_div, 17 / 5 AS division, POWER(2, 10) AS power_, SQRT(144) AS sqrt_;| round_2 | round_hundreds | truncate_1 | ceil_ | floor_ | abs_ | mod_ | int_div | division | power_ | sqrt_ |
|---|---|---|---|---|---|---|---|---|---|---|
| 1234.57 | 1200 | 1234.5 | 5 | 4 | 15 | 2 | 3 | 3.4000 | 1024 | 12 |
ROUND(x, 2)rounds to 2 decimals; a negative precision rounds to tens, hundreds…: 1,200.TRUNCATE(x, 1)cuts after one decimal without rounding: 1234.5.CEILrounds up to the next whole number,FLOORdown.MOD(17, 5)is the remainder (2) and17 DIV 5the whole-number quotient (3)./always returns a decimal in MySQL: 3.4000.
ROUND an average
SELECT dept_id, AVG(salary) AS raw_avg, ROUND(AVG(salary), 1) AS avg_1dp, ROUND(AVG(salary)) AS avg_wholeFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_idORDER BY dept_id;| dept_id | raw_avg | avg_1dp | avg_whole |
|---|---|---|---|
| 1 | 78400.0000 | 78400.0 | 78400 |
| 2 | 69000.0000 | 69000.0 | 69000 |
| 3 | 66500.0000 | 66500.0 | 66500 |
| 4 | 78000.0000 | 78000.0 | 78000 |
AVGreturns an exact decimal with 4 places.ROUND(..., 1)keeps one decimal;ROUND(x)with no precision rounds to a whole number.- Exact values ending in .5 round away from zero, so 63,500.5 would become 63,501.
CEIL: how many boxes?
Each box holds 24 units. How many boxes does each product’s stock need?
SELECT product_name, stock, CEIL(stock / 24) AS boxes_needed, MOD(stock, 24) AS units_in_last_boxFROM productsWHERE stock > 0;| product_name | stock | boxes_needed | units_in_last_box |
|---|---|---|---|
| Wireless Mouse | 120 | 5 | 0 |
| Mechanical Keyboard | 45 | 2 | 21 |
| Office Chair | 12 | 1 | 12 |
| Standing Desk | 5 | 1 | 5 |
| Notebook Pack | 300 | 13 | 12 |
| Gel Pens | 250 | 11 | 10 |
| Desk Lamp | 30 | 2 | 6 |
| Monitor Arm | 18 | 1 | 18 |
stock / 24gives a fraction, such as 120 / 24 = 5 or 45 / 24 = 1.875.CEILrounds up, because a partly filled box is still a box.MOD(stock, 24)shows how many units go in the last box (0 means it is full).
Percentages
Each department’s share of the total payroll:
SELECT dept_id, SUM(salary) AS payroll, ROUND(100 * SUM(salary) / (SELECT SUM(salary) FROM employees), 1) AS pct_of_totalFROM employeesGROUP BY dept_idORDER BY payroll DESC;| dept_id | payroll | pct_of_total |
|---|---|---|
| 1 | 392000 | 37.0 |
| NULL | 250000 | 23.6 |
| 2 | 207000 | 19.5 |
| 3 | 133000 | 12.5 |
| 4 | 78000 | 7.4 |
- The subquery
(SELECT SUM(salary) FROM employees)is the company total: 1,060,000. - Each department’s payroll is multiplied by 100 before dividing, which keeps precision.
ROUND(..., 1)formats the percentage.
In MySQL / never truncates: 7 / 2 is 3.5000. In SQL Server, PostgreSQL and SQLite, dividing two integers drops the fraction (7 / 2 = 3), so write 7 * 1.0 / 2 there.
MOD: even and odd
SELECT emp_id, first_name, CASE WHEN MOD(emp_id, 2) = 0 THEN 'even' ELSE 'odd' END AS id_typeFROM employeesWHERE dept_id = 2;| emp_id | first_name | id_type |
|---|---|---|
| 105 | Vikram | odd |
| 106 | Neha | even |
| 107 | Arjun | odd |
Every other row, a common interview question:
SELECT emp_id, first_nameFROM employeesWHERE MOD(emp_id, 2) = 0;| emp_id | first_name |
|---|---|
| 100 | Meera |
| 102 | Rahul |
| 104 | Karan |
| 106 | Neha |
| 108 | Kavya |
| 110 | Sneha |
ABS, SIGN and differences
How far is each salary from 75,000, regardless of direction?
SELECT first_name, salary, salary - 75000 AS difference, ABS(salary - 75000) AS distance, SIGN(salary - 75000) AS above_or_belowFROM employeesWHERE dept_id IN (1, 4)ORDER BY distance;| first_name | salary | difference | distance | above_or_below |
|---|---|---|---|---|
| Priya | 72000 | -3000 | 3000 | -1 |
| Karan | 72000 | -3000 | 3000 | -1 |
| Sneha | 78000 | 3000 | 3000 | 1 |
| Rahul | 98000 | 23000 | 23000 | 1 |
| Asha | 125000 | 50000 | 50000 | 1 |
| Amit | 25000 | -50000 | 50000 | -1 |
Random numbers
RAND() returns a random number from 0 to 1. Pick one random product (fine for small tables; slow on large ones because every row is sorted):
SELECT product_name FROM products ORDER BY RAND() LIMIT 1;A random whole number from 1 to 6, like a dice roll: FLOOR(1 + RAND() * 6).
Exercise: Price with tax
Show each product’s price, the 18% GST amount rounded to 2 decimals, and the total price rounded to a whole number.
| product_name | price | gst | price_with_gst |
|---|---|---|---|
| Wireless Mouse | 799 | 143.82 | 943 |
| Mechanical Keyboard | 3499 | 629.82 | 4129 |
| USB-C Hub | 1999 | 359.82 | 2359 |
| Office Chair | 8999 | 1619.82 | 10619 |
| Standing Desk | 18999 | 3419.82 | 22419 |
| Notebook Pack | 249 | 44.82 | 294 |
| Gel Pens | 199 | 35.82 | 235 |
| Desk Lamp | 1499 | 269.82 | 1769 |
| Monitor Arm | 2799 | 503.82 | 3303 |
Hint 1
ROUND(price * 0.18, 2) and ROUND(price * 1.18).
SELECT product_name, price, ROUND(price * 0.18, 2) AS gst, ROUND(price * 1.18) AS price_with_gstFROM products;| product_name | price | gst | price_with_gst |
|---|---|---|---|
| Wireless Mouse | 799 | 143.82 | 943 |
| Mechanical Keyboard | 3499 | 629.82 | 4129 |
| USB-C Hub | 1999 | 359.82 | 2359 |
| Office Chair | 8999 | 1619.82 | 10619 |
| Standing Desk | 18999 | 3419.82 | 22419 |
| Notebook Pack | 249 | 44.82 | 294 |
| Gel Pens | 199 | 35.82 | 235 |
| Desk Lamp | 1499 | 269.82 | 1769 |
| Monitor Arm | 2799 | 503.82 | 3303 |
Quick quiz
1. What does ROUND(1234.567, -2) return?
A negative precision rounds to the left of the decimal point.
2. What is CEIL(2.01)?
CEIL always rounds up.
3. What does 7 / 2 return in MySQL?
MySQL’s / returns a decimal. Use DIV for integer division.
4. How do you get the remainder of 17 divided by 5?
MOD returns 2. 17 % 5 also works in MySQL.
Numeric functions FAQ
How do I round to 2 decimal places in SQL?
Use ROUND(value, 2). To cut without rounding, use TRUNCATE(value, 2) in MySQL.
What is the difference between CEIL and FLOOR?
CEIL (or CEILING) rounds up to the nearest whole number; FLOOR rounds down.
Why does integer division give a decimal in MySQL?
MySQL’s / operator always returns a decimal result. Use DIV for whole-number division, or FLOOR(a / b).
How do I calculate a percentage in SQL?
Multiply by 100 before dividing and round the result: ROUND(100 * part / total, 1).