Mathematical Functions

SQL ABS Function

2015-07-01 by suresh

In SQL Server, ABS is a Mathematical Function which will return the absolute (Positive) value of the specified expression.

SQL ABS Function Syntax

In SQL Server, The basic syntax of the ABS Function is:

1 2	SELECT ABS (Numeric_Expression) FROM [Source]

In this article we will show you, How to write ABS Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL ABS Function Example 1

ABS Function is used to return the positive value of any numeric value. The following query will show multiple ways to use ABS function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i int SET @i = -25.8 SELECT ABS(@i) AS [Absolute Value] -- Calculating ABS directly SELECT ABS(-27) AS [Absolute Value] -- Calculating ABS directly SELECT ABS(2 + 55 - 77) AS [Absolute Value]

OUTPUT

Analysis

Below lines of code is used to declare Int variable and assigning the value.

1 2	DECLARE @i int SET @i = -25.8

In the below statement, We used ABS function to find the absolute value of the variable @i (It means ABS(-25.8)). We also assigned new name to that result as ‘Absolute Value’ using ALIAS Column.

1	SELECT ABS(@i) AS [Absolute Value]

In the next statement, We used the ABS Function directly on the int value.

1 2	-- Calculating ABS directly SELECT ABS(-27) AS [Absolute Value]

In the below statement, We used the ABS Function directly on the multiple values.

1 2	-- Calculating ABS directly SELECT ABS(2 + 55 - 77) AS [Absolute Value]

It means

ABS(2 + 55 – 77)

ABS (-20) = 20

SQL ABS Function Example 2

ABS Function also allows you to find the absolute values of a column values.  In this example, We are going to find the absolute values for all the records present in [Service Grade] using ABS Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,[Service Grade]       ,ABS([Service Grade]) AS [Absolute Value]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL ACOS Function

2015-07-02 by suresh

In SQL Server, ACOS is a Mathematical Function which is used to calculate the trigonometry Arccosine for the specified expression. Arccosine is also called as inverse of a cosine, Please refer SQL COS Function article to understand the Cosine Function.

SQL ACOS Function Syntax

In SQL Server, The basic syntax of the ACOS Function is:

1 2	SELECT ACOS (Float_Expression) FROM [Source]

TIP: The ACOS function only accepts float values between -1 and 1.

In this article we will show you, How to write ACOS Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL ACOS Function Example 1

ACOS Function is used to calculate the Arccosine for the given angle. The following query will show multiple ways to use ACOS function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i float SET @i = -0.80 SELECT ACOS(@i) AS [Arc Cosine] -- Calculating ACOS directly SELECT ACOS(0.27) AS [Arc Cosine] -- Calculating ACOS directly SELECT ACOS(0.25 + 0.55 - 0.77) AS [Arc Cosine]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = -0.80

In the below statement, We used ACOS function to calculate the Arccosine value for the variable @i (It means ACOS(-0.80)). We also assigned new name to that result as ‘Arc Cosine’ using ALIAS Column.

1	SELECT ACOS(@i) AS [Arc Cosine]

In the next statements, We used the ACOS Function directly on the float values.

SQL ACOS Function Example 2

In this example, We are going to calculate the Arccosine value for all the records present in [Service Grade] using ACOS Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,[Service Grade]       ,ACOS([Service Grade]) AS [Arc Cosine]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL ASIN Function

2015-07-02 by suresh

In SQL Server, ASIN is a Mathematical Function which is used to calculate the trigonometry Arcsine for the specified expression. Arcsine is also called as inverse of a Sine, Please refer SQL SIN Function article to understand the Sine Function.

SQL ASIN Function Syntax

In SQL Server, The basic syntax of the ASIN Function is:

1 2	SELECT ASIN (Float_Expression) FROM [Source]

TIP: The ASIN function only accepts float values between -1 and 1.

In this article we will show you, How to write ASIN Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL ASIN Function Example 1

ASIN Function is used to calculate the Arcsine for given angle. The following query will show multiple ways to use ASIN function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i Float SET @i = -0.80 SELECT ASIN(@i)AS [Arc Sine] -- Calculating ASIN directly SELECT ASIN(0.27)AS [Arc Sine] -- Calculating ASIN directly SELECT ASIN(0.25 + 0.55 - 0.77)AS [Arc Sine]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i Float SET @i = -0.80

In the below statement, We used ASIN function to calculate the Arcsine value for the variable @i (It means ASIN(-0.80)). We also assigned new name to that result as ‘Arc Sine’ using ALIAS Column.

1	SELECT ASIN(@i)AS [Arc Sine]

In the next statements, We used the ASIN Function directly on the float values.

SQL ASIN Function Example 2

In this example, We are going to calculate the Arcsine value for all the records present in [Service Grade] using ASIN Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,[Service Grade] AS Grade       ,ASIN([Service Grade]) AS [Arc Sine]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL ATAN Function

2015-07-02 by suresh

In SQL Server, ATAN is a Mathematical Function which is used to calculate the trigonometry Arctangent for the specified expression. Arctangent is also called as inverse of a Tangent, Please refer SQL TAN Function article to understand the Tangent Function.

SQL ATAN Function Syntax

In SQL Server, The basic syntax of the ATAN Function is:

1 2	SELECT ATAN (Float_Expression) FROM [Source]

In this article we will show you, How to write ATAN Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL ATAN Function Example 1

ATAN Function is used to calculate the Arctangent value for the given angle. The following query will show multiple ways to use ATAN function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i Float SET @i = -208.80 SELECT ATAN(@i) AS [Arc Tangent] -- Calculating ATAN directly SELECT ATAN(0.27) AS [Arc Tangent] -- Calculating ATAN directly SELECT ATAN(22.25 + 78.55 - 140.77) AS [Arc Tangent]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = -208.80

In the below statement, We used ATAN function to calculate the Arc Tangent value for the variable @i (It means ATAN(-208.80)). We also assigned new name to that result as ‘Arc Tangent’ using ALIAS Column.

1	SELECT TAN(@i) AS [SQL Tangent]

In the next statements, We used the ATAN Function directly on the float values.

SQL ATAN Function Example 2

In this example, We are going to calculate the Arctangent value for all the records present in [Service Grade] using ATAN Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,[Service Grade] AS Grade       ,ATAN([Service Grade]) AS [Arc Tangent]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL ATN2 Function

2015-07-02 by suresh

In SQL Server, ATN2 is a Mathematical Function which is used to return the angle between the positive x-axis and the line from the origin to the point (y, x).

SQL ATN2 Function Syntax

In SQL Server, The basic syntax of the ATN2 Function is:

1 2	SELECT ATN2 (Float_Expression, Float_Expression) FROM [Source]

In this article we will show you, How to write ATN2 Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL ATN2 Function Example 1

ATAN Function is used to find the Angle between the positive x-axis and the line from the origin to the point (y, x). Here the x and y values are the two argument we are going to provide for ATN2 Function. The following query will show multiple ways to use ATN2 function.

T-SQL CODE

1 2 3 4 5 6 7 8 9

DECLARE @i Float = 50.05, @j Float = 200.05 SELECT ATN2(@i, @j) AS [ATN2 Function] -- Calculating ATAN directly SELECT ATN2(100.27, 50.27) AS [ATN2 Function] -- Calculating ATAN directly SELECT ATN2(22.45 + 77.55, 100) AS [ATN2 Function]

OUTPUT

Analysis

Below lines of code is used to declare two float variables and assigning the values.

1	DECLARE @i Float = 50.05, @j Float = 200.05

In the below statement, We used ATN2 function to find the angle between those two variables @i and @j. We also assigned new name to that result as ‘ATN2 Function’ using ALIAS Column.

1	SELECT ATN2(@i, @j) AS [ATN2 Function]

In the next statements, We used the ATN2 Functions directly on the float values. For instance below statement will find the angle between the values 100 (22.45 + 77.55), 100

1 2	-- Calculating ATAN directly SELECT ATN2(22.45 + 77.55, 100) AS [ATN2 Function]

SQL ATN2 Function Example 2

In this example, We are going to calculate the Angle between the values present in [Standard Cost] and [Sales Amount] using ATAN Function.

T-SQL CODE

1 2 3 4 5 6

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,ATN2([StandardCost], [SalesAmount]) AS [ATN2 Function]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL CEILING Function

2015-07-01 by suresh

In SQL Server, CEILING is a Mathematical Function used to return the closest integer value which is greater than or equal to the specified expression.

SQL CEILING Function Syntax

In SQL Server, The basic syntax of the CEILING Function is:

1 2	SELECT CEILING (Numeric_Expression) FROM [Source]

In this article we will show you, How to write CEILING Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL CEILING Function Example 1

CEILING Function is used to return the closest integer value which is greater than or equal to given numeric value. The following query will show multiple ways to use CEILING function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i float SET @i = -208.45 SELECT CEILING(@i)AS [SQL CEILING] -- Calculating CEILING directly SELECT CEILING(0.24)AS [SQL CEILING] -- Calculating CEILING directly SELECT CEILING(2.45 + 7.55 - 14.88)AS [SQL CEILING]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = -208.45

In the below statement, We used CEILING function to find the closest integer value of the variable @i (It means CEILING(-208.45)). We also assigned new name to that result as ‘SQL CEILING’ using ALIAS Column.

1	SELECT CEILING(@i)AS [SQL CEILING]

In the next statement, We used the CEILING Function directly on the int value.

1 2	-- Calculating CEILING directly SELECT CEILING(0.24)AS [SQL CEILING]

In the below statement, We used the CEILING Function directly on the multiple values.

1 2	-- Calculating CEILING directly SELECT CEILING(2.45 + 7.55 - 14.88)AS [SQL CEILING]

It means

CEILING (2.45 + 7.55 – 14.88)

CEILING (-4.88) = – 4

SQL CEILING Function Example 2

In this example, We are going to find the closet integer values for all the records present in [Service Grade] using CEILING Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,CEILING([StandardCost]) AS COST       ,CEILING([SalesAmount]) AS SALES       ,CEILING([TaxAmt]) AS TAX       ,CEILING([Service Grade]) AS Grade   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL COS Function

2015-07-01 by suresh

In SQL Server, COS is a Mathematical Function which is used to calculate the trigonometry Cosine for the specified expression. The mathematical formula for this function is:

COS(x) = Length of the Adjacent Side / Length of the Hypotenuse

SQL COS Function Syntax

In SQL Server, The basic syntax of the COS Function is:

1 2	SELECT COS (Float_Expression) FROM [Source]

In this article we will show you, How to write COS Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL COS Function Example 1

COS Function is used to calculate the Cosine for given angle. The following query will show multiple ways to use COS function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE @i float SET @i = 208.45 SELECT COS(@i)AS [SQL Cosine] -- Calculating COS directly SELECT COS(0.24)AS [SQL Cosine] -- Calculating COS directly SELECT COS(0)AS [SQL Cosine] -- Calculating COS directly SELECT COS(1)AS [SQL Cosine]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = 208.45

In the below statement, We used COS function to calculate the Cosine value for the variable @i (It means COS(208.45)). We also assigned new name to that result as ‘SQL Cosine’ using ALIAS Column.

1	SELECT COS(@i)AS [SQL Cosine]

In the next statements, We used the COS Function directly on the float values.

SQL COS Function Example 2

In this example, We are going to calculate the Cosine value for all the records present in [Tax Amount] and [Service Grade] using COS Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[SalesAmount]       ,[TaxAmt]       ,COS([TaxAmt]) AS [Tax Cosine]       ,[Service Grade] AS Grade       ,COS ([Service Grade]) AS [Cosine]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL COT Function

2015-07-01 by suresh

In SQL Server, COT is a Mathematical Function which is used to calculate the trigonometry Cotangent for the specified expression. The mathematical formula for this function is:

COT(x) = 1 / TAN(x)

Please refer SQL TAN Function article to understand the functionality of tangent function.

SQL COT Function Syntax

In SQL Server, The basic syntax of the COT Function is:

1 2	SELECT COT (Float_Expression) FROM [Source]

In this article we will show you, How to write COT Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL COT Function Example 1

COT Function is used to calculate the Cotangent value for the given angle. The following query will show multiple ways to use COT function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE @i float SET @i = 208.45 SELECT COT(@i)AS [SQL Cotangent] -- Calculating COT directly SELECT COT(0.24)AS [SQL Cotangent] -- Calculating COT directly SELECT COT(10.232)AS [SQL Cotangent] -- Calculating COT directly SELECT COT(1)AS [SQL Cotangent]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = 208.45

In the below statement, We used COT function to calculate the Cotangent value for the variable @i (It means COT(208.45)). We also assigned new name to that result as ‘SQL Cotangent’ using ALIAS Column.

1	SELECT COT(@i)AS [SQL Cotangent]

In the next statements, We used the COT Function directly on the float values.

SQL COT Function Example 2

In this example, We are going to calculate the Cotangent value for all the records present in [Tax Amount] and [Service Grade] using COT Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[SalesAmount]       ,[TaxAmt]       ,COT([TaxAmt]) AS [Tax Cotangent]       ,[Service Grade] AS Grade       ,COT ([Service Grade]) AS [COT Function]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL FLOOR Function

2015-07-01 by suresh

In SQL Server, FLOOR is a Mathematical Function used to return the closest integer value which is less than or equal to the specified expression.

SQL FLOOR Function Syntax

In SQL Server, The basic syntax of the FLOOR Function is:

1 2	SELECT FLOOR (Numeric_Expression) FROM [Source]

In this article we will show you, How to write FLOOR Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL FLOOR Function Example 1

FLOOR Function is used to return the closest integer value which is less than or equal to given numeric value. The following query will show multiple ways to use FLOOR function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10

DECLARE @i float SET @i = -208.45 SELECT FLOOR(@i)AS [SQL FLOOR] -- Calculating FLOOR directly SELECT FLOOR(0.24)AS [SQL FLOOR] -- Calculating FLOOR directly SELECT FLOOR(2.45 + 7.55 - 14.88)AS [SQL FLOOR]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = -208.45

In the below statement, We used FLOOR function to find the closest integer value of the variable @i (It means FLOOR(-208.45)). We also assigned new name to that result as ‘SQL FLOOR’ using ALIAS Column.

1	SELECT FLOOR(@i)AS [SQL FLOOR]

In the next statement, We used the FLOOR Function directly on the float value.

1 2	-- Calculating FLOOR directly SELECT FLOOR(0.24)AS [SQL FLOOR]

In the below statement, We used the FLOOR Function directly on the multiple values.

1 2	-- Calculating FLOOR directly SELECT FLOOR(2.45 + 7.55 - 14.88)AS [SQL FLOOR]

It means

FLOOR (2.45 + 7.55 – 14.88)

FLOOR (-4.88) = – 5

SQL FLOOR Function Example 2

In this example, We are going to find the closet integer values for all the records present in [Service Grade] using FLOOR Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[Color]       ,FLOOR([StandardCost]) AS COST       ,FLOOR([SalesAmount]) AS SALES       ,FLOOR([TaxAmt]) AS TAX       ,FLOOR([Service Grade]) AS Grade   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL POWER Function

2015-07-02 by suresh

In SQL Server, POWER is a Mathematical Function used to calculate the power for the specified expression.

SQL POWER Function Syntax

In SQL Server, The basic syntax of the POWER Function is:

1 2	SELECT POWER (Float_Expression, Value) FROM [Source]

In this article we will show you, How to write POWER Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL POWER Function Example 1

POWER Function is used to calculate the power of a given numeric value. The following query will show multiple ways to use POWER function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12

DECLARE @i float = 2, @j int = 2.20 SELECT POWER(@i, 3) AS [SQL Power] SELECT POWER(@i, 4) AS [SQL Power] -- Calculating Power directly SELECT POWER(3.20, 3) AS [SQL Power] -- Wrong Value SELECT POWER(@j, 3) AS [SQL Power] SELECT POWER(2.20, 3) AS [SQL Power]

OUTPUT

Analysis

Below lines of code is used to declare float and int variable and assigning the values.

1	DECLARE @i float = 2, @j int = 2.20

In the below statement, We used POWER function to calculate the power of the variable @i. We also assigned new name to that result as ‘SQL Power’ using ALIAS Column.

1	SELECT POWER(@i, 3) AS [SQL Power]

It means,

@i * @i * @i  = 2 * 2 * 2 = 8

In the next statement, we used 4 as second argument. It means 2 * 2 * 2 * 2 = 16

1	SELECT POWER(@i, 4) AS [SQL Power]

In the next statement, We used the integer value as input for the POWER function.

1 2 3

-- Wrong Value SELECT POWER(@j, 3) AS [SQL Power] SELECT POWER(2.20, 3) AS [SQL Power]

It means

POWER (@j, 3) = @j * @j * @j

                        = 2.20 * 2.20 * 2.20 = 10.65

But we are getting 8 as a result because it is rounding the 2.20 value to 2

NOTE: Please use float variables as a input for POWER Function otherwise, you may expect strange results.

SQL POWER Function Example 2

Power Function also allows you to calculate the power of column values. In this example, We are going to calculate the power of three for all the records present in [Sales Amount] using POWER Function.

T-SQL CODE

1 2 3 4 5 6

SELECT [EnglishProductName]       ,[Color]       ,[StandardCost]       ,[SalesAmount]       ,POWER([SalesAmount], 3) AS Sales FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog

SQL SIN Function

2015-07-01 by suresh

In SQL Server, SIN is a Mathematical Function which is used to calculate the trigonometry sine for the specified expression. The mathematical formula for this function is:

SIN(x) = Length of the Opposite Side / Length of the Hypotenuse

SQL SIN Function Syntax

In SQL Server, The basic syntax of the SIN Function is:

1 2	SELECT SIN (Float_Expression) FROM [Source]

In this article we will show you, How to write SIN Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL SIN Function Example 1

SIN Function is used to calculate the sine for given angle. The following query will show multiple ways to use SIN function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE @i float SET @i = 208.45 SELECT SIN(@i)AS [SQL SINE] -- Calculating SIN directly SELECT SIN(0.24)AS [SQL SINE] -- Calculating SIN directly SELECT SIN(0)AS [SQL SINE] -- Calculating SIN directly SELECT SIN(1)AS [SQL SINE]

OUTPUT

Analysis

Below lines of code is used to declare float variable and assigning the value.

1 2	DECLARE @i float SET @i = 208.45

In the below statement, We used SIN function to calculate the sine value for the variable @i (It means SIN(208.45)). We also assigned new name to that result as ‘SQL SINE’ using ALIAS Column.

1	SELECT SIN(@i)AS [SQL SINE]

In the next statements, We used the SIN Function directly on the float values.

SQL SIN Function Example 2

In this example, We are going to calculate the sine value for all the records present in [Tax Amount] and [Service grade] using SIN Function.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [EnglishProductName]       ,[SalesAmount]       ,[TaxAmt]       ,SIN([TaxAmt]) AS [Tax Sine]       ,[Service Grade] AS Grade       ,SIN ([Service Grade]) AS [SIN Function]   FROM [Mathemetical Functions]

OUTPUT

Thank You for Visiting Our Blog