SQL Arithmetic Operators

The SQL Server Arithmetic Operators are used to perform arithmetic operations, such as Addition, Subtraction, Multiplication, and Division on given data (operand values). So, you can use these arithmetic operators to perform mathematical operations on two expressions or table columns in a relational database.

Generally, you can use these SQL Arithmetic Operators in the SELECT statement to perform operations on columns to generate a new column. Apart from that, you can use them in the WHERE clause to restrict or filter the data.

This article shows how to use the Arithmetic Operators to perform mathematical operations on simple variables and table columns. It also covers the best practices to improve the performance, handling NULL values, and division by zero errors.

SQL Arithmetic Operators

The following is the list of five available arithmetic operators in SQL Server to perform mathematical operations.

• Addition (+): Add two numbers
• Subtraction (-): Subtract one from the other.
• Multiplication (*): Multiplies two numbers.
• Division (/): Divides one value by another.
• Modulus (%): It returns the remainder after the division.

From the above, both the + and – operators can be used on datetime and smalldatetime data types to add or subtract days from the given date.

SQL Arithmetic Operators Syntax

The basic syntax of the Arithmetic Operators is as shown below.

SELECT <expression> <arithmetic operator> <expression>
FROM Source
WHERE <condition>

The simplified version of the above syntax is as shown below.

SELECT 
Col1 + Col2 AS Sum
Col1 – Col2 AS Difference
Col1 * Col2 AS Product
Col1 / Col2 AS Quotient
Col1 – Col2 AS Remainder
FROM TableName

SQL Arithmetic Operators Example

Before we get into the example, the following table shows the list of available Arithmetic operators in the server.

SQL Arithmetic Operators	Example
+ Addition	DECLARE @A INT, @B INT SET @A = 10 SET @B = 2 SELECT @A + @B AS Total = 12
– Subtraction	SELECT @A – @B AS Total (10 – 2 = 8)
* Multiplication	SELECT @A * @B AS Total (10 * 2 = 20)
/ Division	SELECT @A / @B AS Total (10 / 2 = 5)
% Modulus	SELECT @A % @B AS Total =0 because 10%2 = 0 (Here remainder is zero). If it is 10 % 3, then it will be 1.

While working with the SQL Server Arithmetic operators, you must remember a few things to avoid some rare errors that you might encounter.

• While multiplying the large numeric values, the result may exceed the maximum limit of the data type. So, always use BIGINT or DECIMAL to save these large values. You can use the CAST function to change the data type.
• Working with floating-point numbers may give an inaccurate result because of the automatic rounding of output. To control this, you can use the ROUND or CAST functions to round the decimals to one, two, or three values.

For this SQL Server Arithmetic Operators demo, we will use a simple random product table derived from AdventureWorksDW with 10 records of orders and sales.

+ (Addition) Operator

The Addition is useful in multiple ways, and this section covers all the possibilities. If we use the addition of SQL Server arithmetic operators on Numerical data in a single or multiple columns, then it will add those values and provide the integer output. For instance, if we want to increase the cost of each product, then we can use this addition to add a constant value. You can write the following query to update the TotalProductCost with new values by adding 50 to the original price.

SELECT ProductName, TotalProductCost, TotalProductCost + 50 AS NewPrice
FROM ProductSale

The above query will add 50 dollars to each product in the table and display the new price list. Instead of adding a static value to the column, the Arithmetic operators allow you to add two or more columns.

Arithmetic Operators (+) on Numerical Columns

Let us see how to perform arithmetic addition on numerical data or integer columns in SQL Server using the + operator. For example, perform addition on two or more integer columns to get the final result.

The following query will add the Sales amount and the tax amount to show the total billing price a customer has to pay (or paid) when they made the purchase.

SELECT [ProductName], [Color], [DealerPrice]
   ,[SalesAmount] + [TaxAmt] AS [Total Amount]
FROM [ProductSale]

Concatenate two string Columns using the SQL Arithmetic Operator (+)

If we use the + (addition) operator between two strings or text columns, the server will combine or perform string concatenation and provide a string output.

For example, the following addition query will concatenate the [EnglishProductName] and [Color] string columns to produce a single text column as output. Here, we used the empty space in-between those two columns to separate them. However, you can try any text or symbols to separate the product name and color.

SELECT [ProductName] + '  ' + [Color] AS [Product Name]
   ,[TotalProductCost],[DealerPrice], [SalesAmount], [TaxAmt]
FROM ProductSale

+ Operator to add days to the Date and Time

Apart from the above-mentioned advantages, you can use the + operator to add the required number of days to the date and time value or column. It is helpful to estimate the delivery date from the order date by adding a standard 7 days.

The following query will add 20 days to each product in the ProductSale table and show the new date.

SELECT Color, OrderDate, OrderDate + 20 
FROM ProductSale

Addition of text and integer data types

Unlike some programming languages, SQL Server can not perform implicit data type conversion. So, when you try to perform the addition of the text (character) value and the integer (numeric) columns or values, the SQL arithmetic operator (+) will raise an error.

Within the following query, the two SELECT statements will raise an error.

SELECT '100.50' + 200

SELECT 'Hello' + 200

– (Subtraction) Operator

The Arithmetic subtraction operator in SQL Server is similar to the minus symbol in mathematics to subtract one value from the other. We can use the subtraction operator on numerical values and dates to delete a given number of days from an existing date. Similar to the Addition operator, you can use the static constant value or two columns to perform subtraction. For instance, the below query subtracts $1 from each product price.

SELECT ProductName, SalesAmount, SalesAmount - 1 AS DiscountPrice
FROM ProductSale

Apply a Discount on Products

When the festival season approaches, the stores are willing to provide discounts on all products ranging from 10% to 50% and more. Let’s say we want to apply a 20% standard discount on all the products available in the table. To do so, you have to write the following query.

SELECT ProductName, Color, SalesAmount, 
   SalesAmount - (SalesAmount * 0.20) AS DiscountPrice
FROM ProductSale

For example, the following query will calculate the Profit Margin of the Owner and the Dealer by subtracting the total product cost from the dealer price.

SELECT [ProductName],[Color],[TotalProductCost] ,[DealerPrice]
      ,[DealerPrice] - [TotalProductCost] AS [Owner Profit]
      ,[SalesAmount] - DealerPrice AS [Dealer Profit]
FROM [ProductSale]

Date Subtraction

Similar to the Addition, you can use this SQL arithmetic subtraction operator to delete the required number of days from the existing date. For instance, the below query will subtract 10 days from the order date column.

SELECT ProductName, Color, OrderDate, 
   OrderDate - 10 AS EstimatedDate
FROM ProductSale

Apart from the above, you can use the subtraction operator to check the number of days passed since the last sale. It is very crucial to understand the business. To keep the query as simple as possible, we are finding the difference in years.

SELECT ProductName, Color, OrderDate, 
   YEAR(GETDATE()) - YEAR(OrderDate) AS Years
FROM ProductSale

* (Multiplication) Operator

The SQL arithmetic multiplication operator is used to multiply one value by another, and it can be two columns or any constant value. For instance, if we want to double the salaries of employees working on a successful project like AI, you can simply multiply their annual salary by 2.

SELECT Name, Age, Email, Salary, Salary * 2 AS NewSalary
FROM employees

If you look at our table, we have the sales amount and the total number of orders placed on each product. To calculate the total sales for each product, you can simply multiply the SalesAmount and the OrderQuantity to get the result.

SELECT ProductName, Color, [OrderQuantity],[SalesAmount], 
   [OrderQuantity] * [SalesAmount] AS TotalSale
FROM ProductSale

In this SQL Server example, we multiplied the original sales by orders.

There are some situations where we have to increase the price of a few high-performing products to increase profits. In such a case, you can use the following query:

SELECT ProductName, Color, OrderQuantity, TotalProductCost, 
   TotalProductCost * 1.5 AS NewPrice
FROM ProductSale
WHERE OrderQuantity > 50 

/ (Division) Operator

The SQL Server Arithmetic division operator is used to perform mathematical division on two operands and returns the quotient. If both the dividend and the divisor are integer values, the result will become an integer; even if there are any decimal values, they will be automatically truncated.

For example, the following Division operator query will calculate the percentage of Tax we are paying for each product. For this, we divide the Sales Amount by the Tax Amount.

SELECT [ProductName],[Color],[TotalProductCost] 
      ,[DealerPrice],[SalesAmount],[TaxAmt]
      ,[SalesAmount] / [TaxAmt] AS [Tax Percent]
  FROM [ProductSale]

In this example, it may be the 12.50 as the standard, but in real time, tax rates change based on the state.

% (Modulus) Operator

The SQL Server arithmetic modulus operator is useful for performing mathematical operations that involve dividing one value by another and obtaining the remainder. For instance, you can use the modulus operator to check whether the column value is even or odd. The remainder of any numeric value divided by 2 is zero if it is even; otherwise, it is odd.

If you have the Employee or Customer table with ID values, you can use the below query to print the customers with even CustID.

SELECT CustID, Name, Email FROM customers
WHERE CustID % 2 = 0;

Let me use this % modulus operator to check whether the product orders happen over the weekend or weekdays. With this information, we can focus more resources on those particular days to increase sales.

The following query uses a CASE statement, and in the first case

• DATEPART(WEEKDAY, OrderDate): The DATEPART function will retrieve the day numbers from 1 to 7, where 1 is Sunday.
• Next, we divide that number by 7.
• The IN operator checks whether the remainder is either 6 or 7.
• If True, Weekend. Otherwise, Weekday.

SELECT [ProductName],[Color], SalesAmount, OrderDate,
CASE 
	WHEN DATEPART(WEEKDAY, OrderDate) % 7 IN (6, 7) THEN 'Weekend'
	ELSE 'Weekday'
END AS OrderDay
FROM ProductSale;

How do SQL Arithmetic Operators Handle NULL values?

When you perform the arithmetic operations on NULL values using any of the above-mentioned arithmetic operators, the answer will be NULL. Remember, NULLs are unique empty values, and they are the blank or zeros. For instance, all the below-mentioned queries will return NULL as the result.

SELECT NULL + 10;
SELECT 10 - NULL;
SELECT 20 * NULL;
SELECT 30 % NULL;
SELECT 40 / NULL;

In real time, there may be some records with NULL values, and while performing the arithmetic operations, you must handle them well. Otherwise, the result set will be NULL, and they are the correct values. The SQL Server provides multiple options to handle NULL values in performing arithmetic operations using those operators.

COALESCE Function

The COALESCE function will choose the non-null value from the given arguments and use that to perform the calculation. In the below query, we used the COALESCE function to replace the NULL values with zero or one.

SELECT COALESCE(NULL, 0) + 10;
SELECT 10 - COALESCE(NULL, 0);
SELECT 20 * COALESCE(NULL, 1);
SELECT 30 / COALESCE(NULL, 1);

The output is

10
10
20
30

ISNULL Function

Similar to the above function, you can use the ISNULL function to replace the NULL values with zeros or ones based on the requirement.

SELECT ISNULL(NULL, 0) + 70;
SELECT 50 - ISNULL(NULL, 0);
SELECT 20 * ISNULL(NULL, 1);
SELECT 90 / ISNULL(NULL, 1);

The output is

70
50
20
90

Divisible by Zero Error

One of the common issues one might face while working with the SQL arithmetic operators, especially when performing the division or modulus operation is divisible by zero. There are some situations where we might get the zeros (wrong entry) or no sale. When we divide the total sales value by the order that contains zeros for some product, it will raise an error. To resolve this, you can use either the CASE statement or the NULLIF() function to avoid these errors.

We have declared two variables of integer type, and @b is zero. When you divide it by @a, it will throw an error. SO, we used the CASE to check whether @b is NULL. If true, replace it by NULL, and anything by NULL is NULL.

DECLARE @a INT, @b INT
   SET @a = 10
  SET @b = 0
SELECT 
 CASE WHEN @b = 0 THEN NULL
   ELSE @a / @b
 END AS Output

The other option is the NULLIF function, and the following query will show you the same.

SELECT 10 / NULLIF(0, 0);

SQL Arithmetic Operators Order of Precedence

The Microsoft SQL Server follows some precedence rules to perform the mathematical calculations while using arithmetic operators. If a statement contains more than one operator, the evaluation process follows a set of rules. For instance, the (+ and * combination) first multiplication, and then addition or subtraction.

TIP: We can use the parentheses to override the evaluation process by predefining them.

The following is the list of orders that the order of execution works.

1. Parentheses: Expressions within them execute first.
2. Multiplication and Division: Both have the same priority. If there are no parentheses, these operators will execute in the first place. However, if both are present (* and /), it follows the left-to-right order.
3. Addition and Subtraction: Both have the same priority, and they execute after the above two. If both + and – are present, it follows the left-to-right order.

The table below shows you the simple examples to understand the order of the SQL Server arithmetic operators execution in real-time operations.

Code	Result	Explanation
SELECT 10 + 5 * 3	25	First: Multiplication (5 * 3). Second: Addition 10 + 15
SELECT 10 + 20 / 4	15	First: Division (20 / 4 = 5) Second: 10 + 5 = 15
SELECT 5 * 3 + 20 / 4	20	Both have the same priority. So, left to right First: Multiply (5 * 3 = 15) Second: Divide (20 / 4 = 5) Third: 15 + 5 = 20
SELECT (10 + 5) * 4	60	Because parentheses come first, 10 + 5 = 15. Second: 15 * 4 = 60.
SELECT (20 + 10) * 4 / 5 – 4	20	First: (20 + 10) = 30 Second: 30 * 4 = 120 Third: 120 / 5 = 24 Fourth: 24 – 4 = 20