Arithmetic operators in SQL Server

In this SQL Server tutorial, we will discuss what are Arithmetic operators in SQL Server.

• How to perform arithmetic operations in SQL Server
• SQL Server add operator
• SQL add all values in column
• SQL Server minus operator
• How to subtract two column values in SQL Server
• SQL Server multiplication operator
• How to multiply two column values in SQL Server
• SQL Server divide operator
• SQL divide one column by another
• SQL Server modulo operator

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 got executed. Now let us see how you can use this operator in a 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:

USE BackupDatabase
GO
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.

Read How to export data from SQL Server to Excel

SQL Server minus operator

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

USE BackupDatabase
GO
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:

USE BackupDatabase
GO
EXEC dbo.Subtract 792, 689

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

Also read, SQL Server Substring Function [9 Examples]

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.

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

You can see in the above image that we got the difference of two column values for all the rows in the table.

Read SQL Server Port

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.

USE BackupDatabase
GO
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.

USE BackupDatabase
GO
EXEC dbo.multiply 25, 10

You can see that we got the product of two numbers printed in the output. In this way, you can multiply numbers in SQL Server using the multiplication operator(*).

Read SQL Server Convert Function + Examples

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.

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

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.

Read SQL Server Convert Datetime to date

SQL Server divide operator

In SQL Server, if you want to perform a division operation on any two numbers, you can use the division operator(/). Let us create a stored procedure that will take two numbers, perform division on them and print the result.

USE BackupDatabase
GO
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.

USE BackupDatabase
GO
EXEC dbo.divide 60, 3

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

Read How to create functions in SQL Server Management Studio

SQL divide one column by another

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

USE BackupDatabase
GO
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.

Read SQL Server Convert Datetime to date

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:

1. Dividend: It is the number that we want to divide.
2. 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.

USE BackupDatabase
GO
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.

Read SQL Operand data type real is invalid for modulo operator

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 where I will store only integers in the columns, let us say dbo.ints.

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

You can see in the above output, we 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.

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

• How to perform arithmetic operations in SQL Server
• SQL Server add operator
• SQL add all values in column
• SQL Server minus operator
• How to subtract two column values in SQL Server
• SQL Server multiplication operator
• How to multiply two column values in SQL Server
• SQL Server divide operator
• SQL divide one column by another
• SQL Server modulo operator
• Modulus operator in columns

Related SQL server tutorials:

• How to test stored procedure in SQL Server
• How to get trigger definition in SQL Server
• SQL Server scheduled stored procedure
• SQL Server stored procedure case statement
• Alter Stored Procedure in SQL Server
• Rename stored procedure in SQL Server