SQL Arithmetic operators

You can use the Arithmetic operators in the SELECT clause as well as in WHERE Clause.

Plus operator(+)

Sum two numbers.

SELECT StandardCost, ListPrice, StandardCost + ListPrice AS SumListPriceAndCost
FROM Production.Product
WHERE StandardCost > 0;

‘As’ Keyword is used as an Alias. Alias is the temporary name given to a column. If you do not use any Alias here, the result will show a column name which is not meaningful so to make the query better, you can use alias which is just a temporary name given to the column.

Order Of execution in SQL

Please note that if you code like below –

SELECT StandardCost, ListPrice, StandardCost + ListPrice AS SumListPriceAndCost
FROM Production.Product
WHERE SumListPriceAndCost > 0;

Then the query will fail because the order in which Query gets executed is –

FROM -> WHERE -> SELECT

So, SQL Server Engine is not able to recognize ‘SumListPriceAndCost’ in the WHERE clause because SQL Server analyzes the WHERE clause before the SELECT statement.

Although, you can code like this-

SELECT StandardCost, ListPrice
FROM Production.Product
WHERE  StandardCost + ListPrice > 100;

Or, you can also include the sum in the SELECT statement in SELECT clause as well as WHERE clause like this –

SELECT StandardCost, ListPrice, StandardCost + ListPrice AS SumListPriceAndCost
FROM Production.Product
WHERE  StandardCost + ListPrice > 100;

Note You can use the arithmetic operators in SELECT as well as WHERE clause

Minus operator(-)

Difference between 2 Numbers(Subtraction)

SELECT StandardCost, ListPrice
FROM Production.Product
WHERE  ListPrice - StandardCost > 10;

Multiplication operator(*)

Multiply two numbers.

SELECT ProductID, Name, ReorderPoint, StandardCost
FROM Production.Product
WHERE  ReorderPoint*StandardCost > 80000;

Division operator(/)

Divide the first number by the second number.

SELECT ProductID, Name, ReorderPoint, StandardCost
FROM Production.Product
WHERE  ReorderPoint/StandardCost > 3 AND StandardCost <> 0;

Here, we are using the AND operator to convey to the sql server engine that ‘StandardCost’ must not be equal to 0.

Note You have to be careful when using the division operator.
For example , say a/b means that a is the dividend and b is the divisor and divisor cannot be 0.

Example

If you write –

SELECT ProductID, Name, ReorderPoint, StandardCost
FROM Production.Product
WHERE  ReorderPoint/StandardCost > 3;

You will get an error if any of the records contains ‘StandardCost’ as 0. So, you must be very careful while using this.