
Latest SQL Server Tips
Free SQL Server Learning
How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS 

By: Jeremy Kadlec  Read Comments (14)  Related Tips: More > 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 TSQL 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 TSQL language. Here are the examples we will cover:
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 SELECT 1 + 2 + 3 + 4 + 99 + 704 SELECT 1.5 + 1.5 SELECT .25678 + .00096356 SELECT 1.75 + 2.25  Example 2  Subtraction () SELECT 1  1 SELECT 918  704 SELECT 3.2  1.9 SELECT 1.9  3.2 SELECT 9  3  3 SELECT .75  .68  Example 3  Multiplication (*) SELECT 1 * 1 SELECT 2 * 4 SELECT 2 * 5 * 10 SELECT 1.25 * 3 SELECT .4 * .5  Example 4  Division (/) SELECT 1/2 SELECT 1.0/2.0 SELECT 0/5 SELECT 100/12 SELECT 100.0/12.0 SELECT 75.0/4.5 SELECT .5/.1  Example 5  Combination SELECT ((100 + 100) * .05) SELECT (10  5)/2 SELECT (10.0  5.0)/2.0 SELECT ((100 + 100)  (50 + 50)) 
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  Example 1  Addition (+) SELECT @d2 + @d3  Example 2  Subtraction () SELECT @d2  @d3  Example 3  Multiplication (*) SELECT @d2 * @d3  Example 4  Division (/) SELECT @d2 / @d3  Example 5  Combination SELECT ((@i1 + @i2)  (@d1 + @d2)) 
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  Verify Insertion  Declare Variables  Initialize Variables  Calculate Values 
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  Review the values 
Next Steps
Monday, March 02, 2015  4:04:52 AM  Sana  Read The Tip 
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  Read The Tip 
dharm, 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, 
Wednesday, April 16, 2014  3:46:14 PM  dharm  Read The Tip 
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  Read The Tip 
Sorry about the formatting... 
Wednesday, February 12, 2014  2:36:02 PM  Aaron Bertrand  Read The Tip 
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 FROM (
SELECT b.fullName, COUNT(*)as SaveCount, ) AS x;

Wednesday, December 11, 2013  1:09:26 PM  kilodelta  Read The Tip 
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 subquery where FailCount is derived.
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:
Is there some special reference I need to put on it? 
Tuesday, October 22, 2013  1:34:17 PM  daniel  Read The Tip 
very helpful document ... 
Wednesday, January 30, 2013  9:42:28 PM  Jeremy Kadlec  Read The Tip 
Sri, I am not sure I understand your question 100%, but I believe you are asking about the ISNULL function (http://www.mssqltips.com/sqlservertip/2689/decidingbetweencoalesceandisnullinsqlserver/). I assume you are looking for: SELECT ISNULL(A,0) AS A, ISNULL(B,0) AS B, ISNULL(A,0) + ISNULL(B,0) AS Value HTH. Thank you, 
Wednesday, January 30, 2013  1:24:51 PM  Sri  Read The Tip 
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  Read The Tip 
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 else
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  Read The Tip 
I have an application the User has to pay Total amount in different installments.How to manage Total amount,Payments and TotalSum(Payments) and show in gridview from Sqlserver database in each and every transaction 
Friday, August 24, 2012  9:30:07 AM  Jeremy Kadlec  Read The Tip 
Seema, 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, 
Friday, August 24, 2012  7:45:12 AM  seema sharma  Read The Tip 
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  Read The Tip 
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 codesection; but what I was looking at here appeared to me an independent table that would presumably live adinfinitum 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 dataset 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 packagebody; 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... 