MySQL Arithmetic Operators – Perform Calculations in SQL Queries

Introduction – Why Use Arithmetic Operators in MySQL?

MySQL isn’t just about storing data—it’s also about transforming and analyzing it. Whether you’re calculating totals, applying discounts, computing averages, or adjusting salaries, arithmetic operators enable you to perform mathematical operations directly inside SQL queries.

These operators are foundational for e-commerce platforms, billing systems, analytics tools, and financial applications.

In this guide, you’ll learn:

• The core arithmetic operators in MySQL
• How to apply them in SELECT, UPDATE, and WHERE clauses
• Best practices for avoiding division errors and NULL values
• Real-world examples for business logic

MySQL Arithmetic Operators Overview

Examples – How to Use Arithmetic Operators

1. Addition +

SELECT price + tax AS total_price FROM products;

Adds tax to each product’s price.

2. Subtraction -

SELECT budget - spent AS balance FROM projects;

Calculates remaining budget for each project.

3. Multiplication *

SELECT quantity * unit_price AS total_cost FROM orders;

Computes total cost per order.

4. Division /

SELECT total / item_count AS avg_cost FROM inventory;

Be careful to handle division by zero.

5. Modulo %

SELECT employee_id % 2 AS group_type FROM employees;

Used to segment records into even/odd groups or batches.

Using Arithmetic in UPDATE Queries

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

Applies a 10% salary increment to the sales team.

Using Arithmetic in WHERE Clause

SELECT * FROM orders
WHERE total_price - discount > 100;

Filters orders where net price exceeds 100.

Best Practices for Arithmetic Operators

Real-World Use Cases

Summary – Recap & Next Steps

Arithmetic operators in MySQL help you compute, compare, and analyze numeric values directly in SQL without relying on external applications. They simplify logic in dashboards, reports, and automation scripts.

Key Takeaways

• Use +, -, *, /, % for core math in queries
• Embed arithmetic inside SELECT, WHERE, and UPDATE
• Watch for NULL and division errors
• Combine with ROUND(), IFNULL(), and conditional expressions for robustness

Real-World Relevance

You’ll use arithmetic operators across inventory systems, pricing models, financial reporting, performance scoring, and any logic-driven SQL application.

FAQ – MySQL Arithmetic Operators

What happens if I divide by zero in MySQL?

MySQL returns NULL or may raise an error depending on context. Use safe guards like:

SELECT total / IF(item_count = 0, 1, item_count);

Can arithmetic operators be used with NULL values?

Arithmetic with NULL results in NULL. Use IFNULL() to default:

SELECT IFNULL(salary, 0) + IFNULL(bonus, 0);

Can I chain arithmetic operations?

Yes, use parentheses for clarity and control:

SELECT (price + tax - discount) * quantity AS final_total;

Is % the same as MOD() in MySQL?

Yes. Both return the remainder after division:

SELECT 10 % 3;      -- 1
SELECT MOD(10, 3);  -- 1

Can I use arithmetic in HAVING clause?

Yes, after aggregation:

SELECT customer_id, SUM(amount) AS total
FROM payments
GROUP BY customer_id
HAVING total - 100 > 500;