**Calculating Mathematical Values in SQL Server :**

**Problem:**

In our application we have the need to perform mathematical calculations.Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations.

**Does SQL Server perform basic mathematical calculations?****Solution: **Yes - SQL Server can perform basic addition, subtraction, multiplication and division. So if you need some of those basic building blocks those are available and we will walk through some examples in this tip.

In addition, SQL Server can calculate SUM, COUNT, AVG, etc. For these type of calculations, check out SQL Server T-SQL Aggregate Functions.

To address the multiple code issues, I would recommend researching stored procedures. This tip Getting started with SQL Server stored procedures is probably a good place to start.In this tip, let's focus on some basic building blocks to perform your calculations with the T-SQL language.

Here are the examples we will cover:

• Calculations on Values

• Calculations on Parameters

• Calculations on Columns

• Calculations in Computed Columns

**Calculations on Values:**

As a starting point, values themselves can have mathematical operations performed on them. So in the code below we are performing addition (+), subtraction (-), multiplication (*), division (/) and a combination of operations.

In these examples, both positive and negative whole numbers and decimals are used. The value calculated by the statement is below the code to serve as a point of reference. Review and copy the code into a SQL Server 2005 Management Studio window to get a feel for these calculations. Certainly this list is not comprehensive, so feel free to test some operations in your applications or from your experiences.

**Calculations on Values:****-- Example 1 - Addition (+) :**

SELECT 1 + 1

-- = 2

SELECT 1 + 2 + 3 + 4 + 99 + 704

-- = 813

SELECT 1.5 + 1.5 -- = 3.0

SELECT .25678 + .00096356

-- = 0.25774356

SELECT 1.75 + -2.25

-- = -0.50

**Example 2 - Subtraction (-):**

SELECT 1 - 1-- = 0

SELECT 918 - 704-- = 214

SELECT 3.2 - 1.9-- = 1.3

SELECT 1.9 - 3.2-- = -1.3

SELECT 9 - 3 - 3 -- = 3

SELECT .75 - .68-- = 0.07--

**Example 3 - Multiplication (*):**

SELECT 1 * 1-- = 1

SELECT 2 * -4-- = -8

SELECT 2 * 5 * 10-- = 100

SELECT 1.25 * 3-- = 3.75

SELECT .4 * .5-- = .20--

**Example 4 - Division (/):**

SELECT 1/2-- = 0

SELECT 1.0/2.0-- = 0.500000

SELECT 0/5-- = 0

SELECT 100/12-- = 8

SELECT 100.0/12.0-- = 8.333333

SELECT -75.0/4.5-- = -16.666666

SELECT .5/.1-- = 5.000000-- Example 5 - Combination

SELECT ((100 + 100) * .05)-- = 10.00

SELECT (10 - 5)/2-- = 2

SELECT (10.0 - 5.0)/2.0-- = 2.500000

SELECT ((100 + 100) - (50 + 50))-- = 100

**Calculations on Parameters:**

Since we have covered the bulk of the calculations in the section above let's just focus on a few different operations to show how parameters with specific data types play an important role. Since this set of examples are not comprehensive, feel free to copy the code below into a SQL Server 2005 Management Studio window and test the code with some of your own logic.

Calculations on Parameters

-- Variable declaration

DECLARE @i1 int

DECLARE @i2 int

DECLARE @i3 int

DECLARE @d1 decimal(10,2)

DECLARE @d2 decimal(10,2)

DECLARE @d3 decimal(10,2)

-- Initialize variables

SET @i1 = 100

SET @i2 = 75

SET @i3 = 50

SET @d1 = 1.5

SET @d2 = 5.5

SET @d3 = .575--

**Example 1 - Addition (+)**

SELECT @i1 + @i2 + @i3 + @d1 + @d2 + @d3 -- = 232.58

SELECT @d2 +

-@d3-- = 4.92--

**Example 2 - Subtraction (-):**

SELECT @i2 - @i3

-- = 25

SELECT @d2 - @d3

-- = 4.92

**-- Example 3 - Multiplication (*):**

SELECT @i2 * @i3

-- = 3750

SELECT @d2 * @d3

-- = 3.1900

**-- Example 4 - Division (/):**

SELECT @i2 / @i3

-- = 1

SELECT @d2 / @d3

-- = 9.4827586206896

**-- Example 5 - Combination:**

SELECT ((@i1 + @i2) * @d2)

-- = 962.50

SELECT ((@i1 + @i2) - (@d1 + @d2))

-- = 168.00

**Calculations on Columns:**

Another option is to calculate the values based on a SELECT statement as the example below shows. If the data is in 1 or more columns, this approach eliminates the need to calculate the values based on additional parameters or logic.

Calculations on Columns

-- Sample Table

CREATE TABLE dbo.CalculationExample

(

ProductID int NOT NULL,

Cost decimal(10,2) NOT NULL

)

GO

-- Populate Table

INSERT INTO dbo.CalculationExample (ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00UNION SELECT 3, 25.00

GO

-- Verify Insertion

SELECT * FROM dbo.CalculationExample

GO

-- Declare Variables

DECLARE @MarginPercent decimal(10, 2)

DECLARE @TaxPercent decimal(10, 2)

-- Initialize Variables

SET @MarginPercent = .20

SET @TaxPercent = .05

-- Calculate Values

SELECT ProductID, Cost, Cost * @MarginPercent AS 'Margin',

Cost * @TaxPercent AS 'Tax',

Cost + (Cost * @MarginPercent) + (Cost * @TaxPercent) AS 'FinalCost'

FROM dbo.CalculationExample

GO

**Calculations in Computed Columns:**

Let's take those same calculations and now move them to computed columns in the table. Here is that example:

Calculations in Computed Columns

CREATE TABLE [dbo].[CalculationExample_ComputedColumns]

([ProductID] [int] NOT NULL,

[Cost] [decimal](18, 0) NOT NULL,

[Tax] AS ([Cost]*(0.05)),

[Margin] AS ([Cost]*(0.20)),

[FinalCost] AS (([Cost]+[Cost]*(0.20))+[Cost]*(0.05)),

CONSTRAINT [PK_CalculationExample_ComputedColumns] PRIMARY KEY CLUSTERED ([ProductID] ASC)

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

-- Populate Table

INSERT INTO dbo.CalculationExample_ComputedColumns

(ProductID, Cost)

SELECT 1, 100.00

UNION

SELECT 2, 50.00

UNION

SELECT 3, 25.00

GO

-- Review the values

SELECT * FROM dbo.CalculationExample_ComputedColumnsGO

## No comments:

Post a Comment