SQL Arithmetic Operators

The SQL Arithmetic Operators are used to perform arithmetic operations, such as Addition, Subtraction, Multiplication, and Division on given Data. This article shows how to use the SQL Server Arithmetic Operators to perform arithmetic operations on simple variables and table columns.

SQL Arithmetic Operators Example

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

Operators OperationExample
+AdditionDECLARE @A INT, @B INT
SET @A = 10
SET @B = 2
SELECT @A + @B AS Total = 12
SubtractionSELECT @A – @B AS Total (10 – 2 = 8)
*MultiplicationSELECT @A * @B AS Total (10 * 2 = 20)
/DivisionSELECT @A / @B AS Total (10 / 2 = 5)
%Modulus – It returns the remainder after the divisionSELECT @A % @B AS Total =0 because 10%2 = 0 (Here remainder is zero). If it is 10 % 3, then it will be 1.

For this SQL Server Arithmetic Operators demo, we will use the below-shown data.

Source Tabel Data

SQL + Operator (Addition)

The Addition is useful in multiple ways. If we use the addition of SQL arithmetic operators on Numerical data, then it will add those values and provide the integer output. If we use the + in between two strings, then it will combine them and provide string output.

For example, the following addition query will combine the [EnglishProductName] and [Color] columns

SELECT [EnglishProductName] + ' - ' + [Color] AS [Product Name]
      ,[StandardCost]
      ,[DealerPrice]
      ,[SalesAmount]
      ,[TaxAmt]
  FROM [Product Sales]

NOTE: ‘-‘ is added between the product name and color

Arithmetic Operators in SQL Server 1

Let us see, How to perform Addition on Numerical Data in Sql Server. For example, if we want to increase the standard cost of each product, then we can use this addition to add the constant value.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost] + 2.25 AS [New Standard Cost]
      ,[DealerPrice]
      ,[SalesAmount]
      ,[TaxAmt]
  FROM [Product Sales]

Above SQL Addition arithmetic Operator Query is adding 2.25 to the standard cost of every product present in the Product Sales table.

Addition

SQL Arithmetic Operator Subtraction

The Subtract is used to subtract one value from the other. For example, The following query will calculate the Profit Margin by subtracting the standard cost from the dealer price.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost] 
      ,[DealerPrice]
      ,[DealerPrice] - [StandardCost] AS [Product Margin]
      ,[SalesAmount] 
      ,[TaxAmt]
  FROM [Product Sales]
Arithmetic Operators in SQL Server 3

Multiplication Example

The Multiplication Operator is used to Multiply one value with the other. For example, the following query will calculate the Tax for late payments.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost] 
      ,[DealerPrice]
      ,[SalesAmount] 
      ,[TaxAmt] * 2 AS [Double Taxation]
  FROM [Product Sales]

In this SQL Server example, we multiply the original Tax amount by 2.

Multiplication 4

Division Example

The Division is used to Divide one value with the other. For example, The following SQL Arithmetic Operators query will calculate the percentage of Tax we are paying for each product.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost] 
      ,[DealerPrice]
      ,[SalesAmount] 
      ,[TaxAmt]
      ,[SalesAmount] / [TaxAmt] AS [Tax Percent]
  FROM [Product Sales]
SQL Arithmetic Operators Division 5
Categories SQL