Arithmetic Operators in SQL

Arithmetic operators in SQL are operators that are used to perform mathematical calculations on numeric values. You can use them with numbers, table columns, and expressions to calculate prices, quantities, salaries, discounts, taxes, totals, and other derived values.

The most commonly used arithmetic operators are:

To see how these operators work in practice, we will use a sample products table throughout this section.

Sample Products Table

Suppose we have the following table:

We can use arithmetic operators to perform calculations on this data.

SQL Addition Operator (+)

The addition operator (+) in SQL is an arithmetic operator that adds two or more numeric expressions. You can use the addition operator between two columns, between a column and a literal numeric constant (e.g., Price – 100), or within composite mathematical expressions.

Syntax

SELECT column1 + column2 AS alias_name
FROM table_name;

In this syntax:

• SELECT: The SELECT clause tells the database engine which data to fetch and return in the query result set.
• column1 and column2: These represent column names whose values are used in the arithmetic expression. For the + operator, the values must be compatible numeric data types (such as INT, DECIMAL, FLOAT, or NUMERIC).
• Operator (+): The addition operator adds the values of column1 and column2 for each row included in the query result. The calculation produces a derived value; it does not modify the original values stored in either column.
• AS: This is a keyword used to define a column alias (an alternate label).
• alias_name: This represents the name assigned to the calculated expression. If you do not specify an alias, the database system automatically generates a label, or there is no explicit column name.
• FROM table_name: It specifies the target table from which the engine retrieves the records containing column1 and column2.
• ; (semicolon): It terminates the SQL statement in environments that use semicolons as statement terminators.

Example 1: Calculating Final Cost Using the Addition Operator (+)

Let’s write an SQL query to calculate the final customer cost for each item by adding a fixed shipping fee of 50 to the product price. To find the final payable amount, add a constant literal value (50) to the Price column using the addition arithmetic operator (+).

SQL Query

SELECT ProductID,
       ProductName,
       Price,
       Price + 50 AS FinalPrice
FROM Products;

Query Output

In this example:

• SELECT ProductID, ProductName, Price: Retrieves the product ID, product name, and original price from the Products table.
• Price + 50: Uses the addition operator (+) to calculate a new value by adding 50 to the Price value for each returned row.
• AS FinalPrice: Assigns the alias FinalPrice to the calculated expression, making the result easier to understand in query results, reports, and application code.

The expression produces a derived value in the query result. It does not change the Price value stored in the Products table.

SQL Subtraction Operator (-)

The subtraction operator (-) in SQL is an arithmetic operator used to subtract one numeric expression from another. You can use the subtraction operator between two columns, between a column and a literal numeric constant (e.g., Price – 100), or within composite mathematical expressions.

Syntax

SELECT column1 - column2 AS alias_name
FROM table_name;

Example 2: Calculating Discounted Price Using the Subtraction Operator

Let us write an SQL query to calculate the final selling price of each product by subtracting the promotional discount from the base price. To find the net customer payable amount per unit, deduct the value of the Discount column from the Price column using the – operator.

SQL Query

SELECT ProductID,
       ProductName,
       Price,
       Discount,
       Price - Discount AS DiscountedPrice
FROM Products;

Query Output

SQL Multiplication Operator (*)

The multiplication operator (*) in SQL is an arithmetic operator used to calculate the product of two or more numeric expressions. You can multiply two table columns, multiply a column by a fixed numeric literal, or use it within composite mathematical formulas.

Syntax

SELECT column1 * column2 AS alias_name
FROM table_name;

Example 3: Calculating Total Stock Value

Let us write an SQL query to determine the total stock value of each product held in stock by multiplying the unit price by the quantity. To calculate the total stock value of each product, multiply the value of the Price column by the Quantity column using the * operator.

SQL Query

SELECT ProductID,
       ProductName,
       Price,
       Quantity,
       Price * Quantity AS TotalStockValue
FROM Products;

Query Output

SQL Division Operator (/)

The division operator (/) is an arithmetic operator used to divide the numerator (left operand) by the denominator (right operand). You can use it to divide two table columns, divide a column by a fixed numeric literal, or use it within composite mathematical formulas.

Syntax

SELECT column1 / column2 AS alias_name
FROM table_name;

Example 4: Splitting a Discount into Two Vouchers

Suppose a store offers a discount that is split equally into 2 shopping vouchers. Let’s write an SQL query to calculate the discount amount for each voucher.

SQL Query

SELECT ProductID,
       ProductName,
       Discount,
       Discount / 2.0 AS DiscountPerVoucher
FROM Products;

Query Output

Common Beginner Mistake: Losing Decimal Points

When you divide whole numbers in SQL, databases like SQL Server or PostgreSQL cut off the decimal part. For example, 5 / 2 will show as 2 instead of 2.5.

To get the exact decimal answer (2.5), use 2.0 instead of 2. When SQL sees a decimal number in the calculation, it keeps the decimals in your final result. However, MySQL does this automatically and returns 2.5 even for 5 / 2.

SQL Modulo Operator (%)

The modulo operator (%) in SQL is an arithmetic operator that calculates and returns the remainder left after one numeric value is divided by another. You can use it with table columns, fixed numeric values, or mathematical expressions to determine the remainder of a division operation.

Syntax

SELECT column1 % column2 AS alias_name
FROM table_name;

Note: Oracle SQL does not support the % symbol as the modulo operator. Instead, use the built-in MOD() function, such as MOD(column1, column2), to calculate the remainder after division.

Example 5: Counting Unpacked Items

Suppose you want to pack products in pairs (2 per box). Find out how many items remain unpacked for each product.

SQL Query

SELECT ProductID,
       ProductName,
       Quantity,
       Quantity % 2 AS UnpackedItems
FROM Products;

Query Output

Operator Precedence and Composite Calculations

When combining multiple arithmetic operators in one SQL expression, the database follows standard mathematical precedence rules (PEMDAS):

1. Parentheses () (highest priority)
2. Multiplication *, Division /, Modulo % (evaluated left to right)
3. Addition +, Subtraction – (evaluated left to right)

Example 6: Calculating the Total Bill After Discount

Suppose a customer gets a discount on each item. Find the final price for all items.

To find their total bill, subtract the discount from the price first, then multiply by the number of items bought: (Price – Discount) * Quantity.

SQL Query

SELECT ProductID,
       ProductName,
       Price,
       Discount,
       Quantity,
       (Price - Discount) * Quantity AS TotalPayable
FROM Products;

Query Output

Why Parentheses Matter

Without parentheses:

Price - Discount * Quantity

SQL would multiply Discount * Quantity first before subtracting from Price. It would compute 800 – (100 * 2) = 600, which is mathematically incorrect for total order pricing.

Edge Cases to Watch for When Using SQL Arithmetic Operators

When using arithmetic operators in SQL queries, you should account for edge cases such as NULL values and division by zero. You should handle these cases properly to prevent unexpected results and query errors.

1. NULL Propagation

In SQL, arithmetic expressions involving NULL generally evaluate to NULL because NULL represents an unknown or missing value.

For example:

• 500 + NULL → NULL
• 1000 * NULL → NULL

If a column contains NULL values and you want to replace them with a specific default value during a calculation, use the COALESCE() function.

SELECT ProductName,
    Price - COALESCE(Discount, 0) AS DiscountedPrice
FROM Products;

Here, COALESCE(Discount, 0) returns the value of Discount when it is not NULL. If Discount is NULL, it returns 0 instead. Therefore, the calculation can continue using 0 as the default discount.

Important Note: COALESCE() does not modify the stored value in the table. It only substitutes a value within the query result or expression.

2. Division by Zero

Dividing a numeric value by zero can cause a division-by-zero error. The exact behavior is DBMS-specific, but systems such as PostgreSQL, SQL Server, and Oracle can raise an error for ordinary numeric division by zero.

To overcome this error, you should use NULLIF() to safely handle a denominator that may contain zero:

SELECT TotalSales,
UnitsSold,
TotalSales / NULLIF(UnitsSold, 0) AS SalesPerUnit
FROM Sales;

NULLIF(UnitsSold, 0) works as follows:

• If UnitsSold is not 0, NULLIF() returns its original value.
• If UnitsSold is 0, NULLIF() returns NULL.

Dividing by NULL produces NULL rather than performing division by zero. This prevents the division-by-zero error for rows where UnitsSold is 0. For example:

TotalSales = 1000
UnitsSold = 0

1000 / NULLIF(0, 0)
= 1000 / NULL
= NULL

This approach is particularly useful when a zero denominator is a valid possibility in data and you want the query to return NULL instead of failing because of division by zero.