Calculating Mathematical Values in SQL Server

By:   |   Updated: 2008-07-24   |   Comments (16)   |   Related: More > T-SQL

Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.

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?

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 (/)

-- = 0

SELECT 1.0/2.0
-- = 0.500000

-- = 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
@i1 int
@i2 int
@i3 int
@d1 decimal(10,2)
@d2 decimal(10,2)
@d3 decimal(10,2

-- Initialize variables
@i1 = 100
@i2 = 75
@i3 = 50
@d1 = 1.5
@d2 = 5.5
@d3 =

-- Example 1 - Addition (+)
@i1 + @i2 + @i3 + @d1 + @d2 + @d3
-- = 232.58

SELECT @d2 + -@d3
-- = 4.92

-- Example 2 - Subtraction (-)
@i2 - @i3
-- = 25

SELECT @d2 - @d3
-- = 4.92

-- Example 3 - Multiplication (*)
@i2 * @i3
-- = 3750

SELECT @d2 * @d3
-- = 3.1900

-- Example 4 - Division (/)
@i2 / @i3
-- = 1

SELECT @d2 / @d3
-- = 9.4827586206896

-- Example 5 - Combination
((@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
TABLE dbo.CalculationExample(
ProductID int NOT NULL,
Cost decimal(10,2) NOT NULL)

-- Populate Table
INSERT INTO dbo.CalculationExample (ProductID, Cost)
SELECT 1, 100.00
2, 50.00
SELECT 3, 25.00

-- Verify Insertion
FROM dbo.CalculationExample

-- Declare Variables
DECLARE @MarginPercent decimal(10, 2)
DECLARE @TaxPercent decimal(10, 2)

-- Initialize Variables
SET @MarginPercent = .20
@TaxPercent = .05

-- Calculate Values
* @MarginPercent AS 'Margin',
* @TaxPercent AS 'Tax',
+ (Cost * @MarginPercent) + (Cost * @TaxPercent) AS 'FinalCost'

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]
[Cost] [decimal]
(18, 0) NOT NULL,
AS ([Cost]*(0.05)),
AS ([Cost]*(0.20)),
AS (([Cost]+[Cost]*(0.20))+[Cost]*(0.05)),
CONSTRAINT [PK_CalculationExample_ComputedColumns] PRIMARY KEY CLUSTERED


-- Populate Table
INSERT INTO dbo.CalculationExample_ComputedColumns (ProductID, Cost)
SELECT 1, 100.00
2, 50.00
SELECT 3, 25.00

-- Review the values

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:

Last Updated: 2008-07-24

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips

Comments For This Article

Thursday, February 01, 2018 - 4:20:16 AM - Suttichai Back To Top (75075)


I am trying to tranform a calculation from a C# code to a column in store procedure but I am geting a lot of syntax error please help as i am new to Sql.

The calcualtion is about the leave Entitlement base on join date upto to present day .

Below is a part of my store procedure :

declare @PreviousYearCarryOn As int = 0 ;
declare @DiffYear As Int ;
declare @LeaveAvailableDay As int ;
DECLARE @StartDate AS datetime = StartDate ;
DECLARE @YearDiff AS INT = @SelectedYear - Year(GetDATE()) ;
DECLARE @CurrentCalDate AS datetime = DATEADD(YEAR,@YearDiff,Year(GetDATE()));
DECLARE @numberOfDays AS INT = DAY(EOMONTH(DATEFROMPARTS(year(@CurrentCalDate),month(@CurrentCalDate),1))) ;
DECLARE @OneYearDate as DATE = DATEADD(YEAR,1,@StartDate);
DECLARE @TwoYearDate AS datetime = DATEADD(YEAR,2,@StartDate);
DECLARE @ThreeYearDate AS datetime = DATEADD(YEAR,3,@StartDate);
DECLARE @FiveYearDate AS datetime = DATEADD(YEAR,5,@StartDate);
DECLARE @FifteenYearDate AS datetime = DATEADD(YEAR,15,@StartDate);
DECLARE @vAge AS varchar(20);

IF @YearDiff < 0

SET @CurrentEndofMonthDate = @EndofYearDate;

set @DiffYear = @selectedYear - year(@StartDate) ;

IF @DiffYear = 0

set @LeaveAvailableDay = 0;

IF @DiffYear = 1 Then


IF @CurrentEndofMonthDate >= @OneYearDate 

set @PreviousYearCarryOn = 0; ----- ;
set @LeaveAvailableDay = 6+ @PreviousYearCarryOn;


set @PreviousYearCarryOn = 0; ----- ;
set @LeaveAvailableDay = @PreviousYearCarryOn;


IF (@DiffYear >= 2 AND @DiffYear < 3)
set @LeaveAvailableDay= 6;

IF (@DiffYear >= 3 AND @DiffYear < 5)


IF (@CurrentEndofMonthDate >= @ThreeYearDate)

set @LeaveAvailableDay= 10;


set @LeaveAvailableDay= 6;


IF (@DiffYear >= 5 AND @DiffYear < 15)

IF (@CurrentEndofMonthDate >= @FiveYearDate)

set @LeaveAvailableDay= 15;


set @LeaveAvailableDay= 10;

IF (@DiffYear >= 3 AND @DiffYear < 5)

IF (@CurrentEndofMonthDate >= @FifteenYearDate)

set @LeaveAvailableDay= 20;


set @LeaveAvailableDay= 15;

RETURN @LeaveAvailableDay ;

Sunday, March 05, 2017 - 1:17:23 AM - MA Back To Top (47240)

 Perfect , thank you very much


Monday, March 02, 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!

Thursday, April 17, 2014 - 11:27:55 AM - Jeremy Kadlec Back To Top (30094)


Thank you for the post.  I am not exactly sure what you are asking.  Are you trying to find every value from the ID column that is divisible by 3?  For example, where ID is equal to 3, 6, 9, 12, 15, 18, etc.?

Thank you,
Jeremy Kadlec Community Co-Leader

Wednesday, April 16, 2014 - 3:46:14 PM - dharm Back To Top (30083)

select * from Table_name Where id/3


Please correct this query

 I want to select  where id devide by any specific number

Wednesday, February 12, 2014 - 2:36:23 PM - Aaron Bertrand Back To Top (29423)

Sorry about the formatting...

Wednesday, February 12, 2014 - 2:36:02 PM - Aaron Bertrand Back To Top (29422)

kilodelta, you've probably long since moved past this problem, but you can't reference an alias at the same scope, since SQL Server could process the elements of the SELECT list in any order. Instead you need to do something like this, where you define the alias in one scope, then reference it in an outer scope:


SELECT fullName, SaveCount, FailCount, GTotal = SaveCount + FailCount
  SELECT  b.fullName, COUNT(*)as SaveCount,
  (SELECT COUNT(status) from dbo.ccp_CUSTOMER_RETENTION_LOG as a, dbo.ccp_users as b with (NOLOCK)   where a.ccp_UsersID = and a.ccp_UsersID = '12341' and status='F' and a.insertDT between '12-06-13'   and '12-11-13' group by b.fullName) as FailCount
from dbo.ccp_CUSTOMER_RETENTION_LOG as a, dbo.ccp_users as b with (NOLOCK) where a.ccp_UsersID = and status='S' and a.insertDT between '12-06-13' and '12-11-13' and a.ccp_UsersID = '12341' group by b.fullName
) AS x;

Wednesday, December 11, 2013 - 1:09:26 PM - kilodelta Back To Top (27766)


I'm trying something on SQL Server 2005 - I'm trying to do math on two fields I extract in the SELECT, they being SaveCount and FailCount. The query uses a sub-query where FailCount is derived.

Here's the sql:

SELECT  b.fullName, COUNT(*)as SaveCount,
(SELECT COUNT(status) from dbo.ccp_CUSTOMER_RETENTION_LOG as a, dbo.ccp_users as b with (NOLOCK) where a.ccp_UsersID = and a.ccp_UsersID = '12341' and status='F' and a.insertDT between '12-06-13' and '12-11-13' group by b.fullName) as FailCount, SaveCount+FailCount as 'GTotal'
from dbo.ccp_CUSTOMER_RETENTION_LOG as a, dbo.ccp_users as b with (NOLOCK) where a.ccp_UsersID = and status='S' and a.insertDT between '12-06-13' and '12-11-13' and a.ccp_UsersID = '12341' group by b.fullName;


I want to do the calculations on SaveCount and FailCount but if I try to say SaveCount + FailCount as 'GTotal' it craps out. I've put it right after FailCount is derived.

I get the following error:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'FailCount'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SaveCount'.


Is there some special reference I need to put on it?

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

very helpful document ...

Wednesday, January 30, 2013 - 9:42:28 PM - Jeremy Kadlec Back To Top (21813)


I am not sure I understand your question 100%, but I believe you are asking about the ISNULL function (

I assume you are looking for:



Thank you,
Jeremy Kadlec
Community Co-Leader 

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?

Monday, October 08, 2012 - 2:30:03 AM - Zitr Back To Top (19815)

i have a two tables one is ledger master and another is sales voucher . Now i need a query which is able to calculate new accesable value

for eg

in Ledger master there is field  named as ''add in accesable value '' it's a tick check box and if it is Yes then it add into accesable value



In sales voucher there is field named as " amount"


the entry through sales voucher is stored in sales voucher tax field amount


now i want a final accesable value should be sum of amount + tax ledger amount


Kindly helep me





Wednesday, September 05, 2012 - 7:37:48 AM - Ramoji Back To Top (19395)

I have an application the User has to pay Total amount in different installments.How to manage Total amount,Payments

and Total-Sum(Payments) and show in gridview from Sqlserver database in each and every transaction

Friday, August 24, 2012 - 9:30:07 AM - Jeremy Kadlec Back To Top (19213)


I do not understand your question 100%.  Can you post some sample data and/or the table structure?

This would help me answer the question.

Thank you,
Jeremy Kadlec

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 ?

Thursday, August 07, 2008 - 7:43:57 AM - manningda Back To Top (1592)
Nice article, but I do wonder about the last section where you are inserting your values to an independent table.  Iíve seen apps in the past where temp tables are used to store calculations for the express purpose of a particular procedure or code-section; but what I was looking at here appeared to me an independent table that would presumably live ad-infinitum and grow as the application is used and calculates more values.Couldn't that be a strain on the system in the long run? Assuming that a calculation is necessary to report an analysis "metric"; and is drawn from data entered by the user(s) --- then I think experience would suggest that both the supporting data for the calculation AND the calculation itself may change over time.For example, maybe you want to know all the statistics for a data-set as they relate to this year's usage of something.  Later on you decide you need to know quarterly data as well  AND additionally want to know how that calculation is relevant to some subset --- say how many children used your product during the 3rd quarter. Obviously the number of ways you can parse these sort of calculations is almost infinite in itself; but the example is intended to illustrate that an effort to calculate and then store the calculations for future use may in the end become a very expensive choice where system resources are concerned.

 Instead one might consider simply using the calculation examples provided and keeping them in a package-body; or stored procedure; or even the .asp code itself.  Then your system "expense" is only when the report/calculation is called by the app --- not in maintaining the history of all calculations over time.

Advantages: 1. No monster table housing calculations that may become irrelevant over time; 2. one less point to "manage" as you tune your db periodically; 3. greater flexibility in the application as your code is calculating and reporting (EX: new report/calculation = new code --- old irrelevant report = comment out or delete.)Just thought it may be relevant to consider --- calculations are very valuable.  Tables of calculated values can be very "expensive" for your storage; tuning and overall system performance as well.

...just a thought...


Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor

get free sql tips
agree to terms