How to Use Arithmetic Operators in SQL Server

While working on the project, I was required to perform some calculations that required me to use arithmetic operators. In SQL Server, arithmetic operations are a fundamental part of data manipulation and querying.

Here, I will discuss using of arithmetic operators in SQL Server.

Table of Contents

How to Perform Arithmetic Operations in SQL Server

While performing queries on a SQL Server database or performing operations in a SQL Server stored procedure, you may want to perform some mathematical operations. In that case, you can use the arithmetic operators provided in SQL Server.

These arithmetic operators are:

Operator Symbol	Meaning and Usage
+	Addition
–	Subtraction
*	Multiplication
/	Division
%	Modulo

Let us discuss each of them with the help of some examples.

SQL Server Add Operator

The SQL Server addition operator(+) can be used to add two numbers in SQL Server. You can use it in a stored procedure to add multiple numeric values stored in variables or stored in any table in the database.

For example, below is the stored procedure to add two numbers:

USE BackupDatabase
GO
CREATE PROCEDURE dbo.Addition @num1 int, @num2 int AS
BEGIN
	DECLARE
		@result int
	SET @result = @num1 + @num2
	PRINT('The sum of two numbers is: ' + str(@result))
END

Let us execute the stored procedure now.

USE BackupDatabase
GO
EXEC dbo.Addition 34, 45

You can see that the sum of two numbers was executed. Now, let’s see how to use this operator in an SQL Server table.

SQL Add all Values in Column

I have created a table having two columns, i.e., two operands on which I will perform the arithmetic operations.

CREATE TABLE dbo.operands(
[First Number] real,
[Second Number] real
)

I will enter store some random numbers in this table. I will be using this table in the below sections also.

INSERT INTO dbo.operands([First Number], [Second Number])
VALUES(45, 65),
	(94, 63),
	(86, 97),
	(1046,969),
	(68, 30)

Now let us use the addition(+) operator to find the sum of two column values in every row:

SELECT [First Number], [Second Number], [First Number] + [Second Number] AS [Sum] FROM dbo.operands

You can see that the sum of two column values is calculated and displayed in another column. In this way, you can add column values in SQL Server using the addition(+) operator.

SQL Server Minus Operator

The minus operator(-), also called the subtraction operator, can be used to find the difference between two numeric values. For example, I will create a stored procedure to subtract two numbers:

CREATE PROCEDURE dbo.Subtract @num1 real, @num2 real AS
BEGIN
	DECLARE
		@result real
	SET @result = @num1 - @num2
	PRINT(' The difference between the two numbers is:' + str(@result))
END

Now, let us execute this stored procedure.

EXEC dbo.Subtract 792, 689

The subtraction of two numbers was displayed in the output. In this way, you can use the minus or subtraction operator(-) to find the difference between two numbers.

How to Subtract Two Column Values in SQL Server

Now, let us use this subtraction operator to find the difference between two column values of a table for multiple rows.

I will use the same table that I created in the above section.

SELECT [First Number], [Second Number], [First Number] - [Second Number] FROM dbo.operands

Arithmetic operators from the SQL Server

The above image shows that we obtained the difference of two column values for all the rows in the table.

SQL Server Multiplication Operator

In SQL Server, you can use the multiplication operator(*) to find the product of numbers. Let us create a stored procedure that will print the product of two numbers.

CREATE PROCEDURE dbo.multiply @num1 real, @num2 real
AS
BEGIN
	DECLARE
		@result real
	SET @result = @num1 * @num2
	PRINT('The product of two numbers is: ' + str(@result))
END

Now, we will execute the stored procedure with two input values.

EXEC dbo.multiply 25, 10

The output shows the product of two numbers. This shows how to multiply numbers in SQL Server using the multiplication operator(*).

How to Multiply Two-Column Values in SQL Server

Now, let us multiply two column values of all the rows of a table in a SQL Server database. I will use the same table that I used in the above section.

SELECT [First Number], [Second Number], [First Number] * [Second Number] AS Product FROM dbo.operands

How to multiply two column values in SQL Server

You can see that we got the product of the two-column values of all the rows in a third column. In this way, you can use the multiplication operator in a table to perform any multiplication operation on columns.

SQL Server Divide Operator

In SQL Server, the division operator (/) allows you to perform a division operation on any two numbers. Let’s create a stored procedure that will take two numbers, divide them, and print the result.

CREATE PROCEDURE dbo.divide @num1 real, @num2 real AS
BEGIN
	DECLARE
		@result real
	SET @result = @num1 / @num2
	PRINT('The quotient after the division operation is: ' + str(@result))
END

Now, let us execute the stored procedure.

EXEC dbo.divide 60, 3

You can see the result of the division operation printed on the output screen.

SQL Divide One Column by Another

Now, let’s see an example of dividing a column by another column. Let’s perform the division operation on the table named dbo.operands.

SELECT [First Number], [Second Number], [First Number] / [Second Number] AS Quotient FROM dbo.operands

You can see that we performed a division operation on the column values stored the quotient values in another column. In this way, you can perform division operations on column values in SQL Server.

SQL Server Modulo Operator

The modulo or the modulus operator(%) in SQL Server can be used to find the modulus of a division operation. The modulus operator takes two arguments:

Dividend: It is the number that we want to divide.
Divisor: It is the number by which we want to divide the dividend.

Let us create a stored procedure that will take these two arguments and print the modulus of the division operation on these two numbers.

CREATE PROCEDURE dbo.modulus @dividend int, @divisor int
AS
BEGIN
	DECLARE
		@result int
	SET @result = @dividend % @divisor
	PRINT('The modulus is: ' + str(@result))
END

Now, let us execute this stored procedure.

USE BackupDatabase
GO
EXEC dbo.modulus 35, 6

You can see that the result, i.e., the modulus, got printed on the output screen.

Modulus Operator in Columns

Now, let us use this modulus operator inside a SELECT statement and find the modulus of two column values.

This time, I cannot use the table dbo.operands because the modulus operation does not work on real numbers. I will create another table, let us say dbo. ints, where I will store only integers in the columns.

SELECT [First Number], [Second Number], [First Number] % [Second Number] AS Modulus FROM dbo.ints

You can see in the above outWe got the modulus of two column values for all the rows and displayed the modulus value in another column. In this way, you can use the modulus operator in columns.

Conclusion

Thus, you might have learned about the arithmetic operators in SQL Server and how to use them in stored procedures or SELECT statements.