Arithmetic Operators

Arithmetic operators perform mathematical operations on two or more than two expressions with same datatypes.

These Operators are same as arithmetic operators in other programming languages.

  • Arithmetic operators supports numeric datatypes like Tinyint, Smallint, Int, Bigint, Decimal, Float…The datatype of the result of the arithmetic operation depends on the datatypes of the expressions used in it.
  • Type conversion is needed when we are using different datatypes in the expression.

SQL Server supports 5 arithmetic operators.

 Arithmetic Operators

/*————————————————— Addition ——————————————————*/

Addition (+):

Addition operator adds two numeric values or Expressions.

Syntax:

Expression1 + Expression2 +……. + Expression N

Examples:

————————————————————————–
— Both Integers – Result – INT
SELECT 10 + 15 — Result 25
————————————————————————–
— One Integer and One Decimal – Result – Decimal
SELECT 2.5 + 10 — Result 12.5
————————————————————————–
— Both Decimals – Result – Decimal
SELECT 1.4 + 3.8 — Result 5.2
————————————————————————–
— One +ve no and One -ve no
SELECT 1.4 + -3.8 — Result -2.4
— Or
SELECT 1.4 + (-3.8) — Result -2.4
— It is a Good practice to use Parenthesis.
————————————————————————–
USE [ADVENTUREWORKS2012]
GO
————————————————————————–
SELECT TOP 5 [AverageRate], [EndOfDayRate],
[AverageRate] + [EndOfDayRate] AS [Addition]
FROM [Sales].[CurrencyRate]
————————————————————————–

Result:

Addition

 /*————————————————– Subtraction —————————————————*/

Subtraction (-):

Subtraction operator subtracts one numeric value (or expression) from another numeric value (or expression).

Syntax:

Expression1 – Expression2

Examples:

————————————————————————–
— Both Integers – Result – INT
SELECT 125 – 24 — Result 101
————————————————————————–
— One Integer and One Decimal – Result – Decimal
SELECT 752.7 – 75 — Result 677.7
————————————————————————–
— Both Decimals – Result – Decimal
SELECT 21.4 – 6.8 — Result 14.6
————————————————————————–
— One +ve no and One -ve no
SELECT 83.4 – -93.8 — Result -177.2
— Or
SELECT 83.4 – (-93.8) — Result -177.2
— It is a Good practice to use Parenthesis.
————————————————————————–
USE [ADVENTUREWORKS2012]
GO
————————————————————————–
SELECT TOP 5 [AverageRate], [EndOfDayRate],
[AverageRate] – [EndOfDayRate] AS [Subtraction]
FROM [Sales].[CurrencyRate]
————————————————————————–

Result:

Subtraction

  /*———————————————— Multiplication ————————————————–*/

Multiplication (*):

Multiplication operator multiplies two numeric values or expressions.

Syntax:

Expression1 * Expression2

Examples:

————————————————————–
— Both Integers – Result – INT
SELECT 63 * 5 — Result 315
————————————————————–
— One Integer and One Decimal – Result – Decimal
SELECT 27.7 * 36 — Result 997.2
————————————————————–
— Both Decimals – Result – Decimal
SELECT 85.4 * 2.8 — Result 239.12
————————————————————–
— One +ve no and One -ve no
SELECT 24.4 * -2.8 — Result -68.32
— Or
SELECT 24.4 * (-2.8) — Result -68.32
— It is a Good practice to use Parenthesis.
————————————————————–
USE [ADVENTUREWORKS2012]
GO
————————————————————–
SELECT TOP 5 [AverageRate], [EndOfDayRate],
[AverageRate] * [EndOfDayRate] AS [Multiplication]
FROM [Sales].[CurrencyRate]
————————————————————–

Result:

Multiplication

/*———————————————— Division ————————————————–*/

Division (/):

Division operator divides two numeric values or expressions.

Syntax:

Dividend / divisor

Examples:

————————————————————–
— Both Integers – Result – INT
SELECT 357 / 5 — Result 71
————————————————————–
— One Integer and One Decimal – Result – Decimal
SELECT 84.7 / 36 — Result 2.352777
————————————————————–
— Both Decimals – Result – Decimal
SELECT 28.4 / 2.8 — Result 10.142857
————————————————————–
— One +ve no and One -ve no
SELECT 423.4 / -2.8 — Result -151.214285
— Or
SELECT 423.4 / (-2.8) — Result -151.214285
— It is a Good practice to use Parenthesis.
————————————————————–
USE [ADVENTUREWORKS2012]
GO
————————————————————–
SELECT TOP 5 [AverageRate], [EndOfDayRate],
[AverageRate] / [EndOfDayRate] AS [Division]
 FROM [Sales].[CurrencyRate]
————————————————————–

Result:

Division

 /*———————————————— Modulus ————————————————–*/

Modulus (%):

Returns the remainder of one numeric value divided by another numeric value.

Syntax:

dividend % divisor

Examples:
————————————————————–
— Both Integers – Result – INT
SELECT 843 % 5 — Result 3
————————————————————–
— One Integer and One Decimal – Result – Decimal
SELECT 63.7 % 36 — Result 27.7
————————————————————–
— Both Decimals – Result – Decimal
SELECT 39.4 % 2.8 — Result 0.2
————————————————————–
— One +ve no and One -ve no
SELECT 764.4 % -2.8 — Result 0.0
— Or
SELECT 764.4 % (-2.8) — Result 0.0
— It is a Good practice to use Parenthesis.
————————————————————–
USE [ADVENTUREWORKS2012]
GO
————————————————————–
SELECT TOP 5 [AverageRate], [EndOfDayRate],
[AverageRate] % [EndOfDayRate] AS [Modulus]
FROM [Sales].[CurrencyRate]
————————————————————–

Result:

Modulus

 

ALL THE BEST

Leave a Reply

Your email address will not be published. Required fields are marked *