Calculating Mathematical Values in SQL Server

By: Jeremy Kadlec | Comments (16) | Related: > TSQL

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.00
UNION 
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',
   Cos t + (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_ComputedColumns
GO

Next Steps

Review the code in each of these sections and test them out for yourself to get a feel for these types of operations.
Review your application and think about different components that require calculations to determine which option best meets the needs.
If you are faced with changing, improving or consolidating calculations, consider the options in this tip as potential options to address those needs.
Check out these related tips:
- SQL Server T-SQL Aggregate Functions
- Getting started with SQL Server stored procedures

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monday, March 2, 2015 - 4:04:52 AM - Sana	Back To Top (36397)
Thank yoU! i have been looking to add values from different columns. Finally found your article. Thanks bunches!

Wednesday, February 12, 2014 - 2:36:23 PM - Aaron Bertrand	Back To Top (29423)
Sorry about the formatting...

Tuesday, October 22, 2013 - 1:34:17 PM - daniel	Back To Top (27228)
very helpful document ...

Wednesday, January 30, 2013 - 1:24:51 PM - Sri	Back To Top (21801)
How to handle null values when performing calculations on columns in the select statement? For example if there are two columns A and B in table T and when the Select query is Select A, B, A+B as Total from T. I get null for total when either A or B is null instead I want to get the value which not null as total. How is it possible?

Friday, August 24, 2012 - 7:45:12 AM - seema sharma	Back To Top (19208)
i m making company employee salary detail by sql server so rell me how can i do ?