Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Performing Mathematical Calculations in Query

Author  Topic 

pattonjo
Starting Member

11 Posts

Posted - 2013-06-11 : 11:20:07
I have two columns that retain data I want to perform calculations on.


(1/(CycleTime/3600)*(Percentage)


*Percentage is defined as float.

How can I define a column (that doesn't exist) as an expression and then perform the above operation?

Thanks Much

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 11:37:18
Sounds that you're looking for computed column. Example

SELECT ID,Name,Salar,Salary*12 AnnualSalary
FROM TableName

Highlighted in red, is how you can use computation in your SQL query to have a computed column at run time.

Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 00:34:02
SELECT CycleTime, Percentage, (1/(CycleTime/3600)*(Percentage) AS NewColumn, <column List>
FROM YourTable

Assumed that CycleTime and Percentage are two column available in the table called "YourTable"
NOTE:
Red marked part is for specifying other columns which you have in that table
Blue marked part represents the alias name for the calculated column

--
Chandu
Go to Top of Page

pattonjo
Starting Member

11 Posts

Posted - 2013-06-13 : 00:43:47
I use:

(1/(CycleTime/3600))*(Percentage/100)

and I get desired results, but how can I handle dividing by zero errors in this particular instance?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 00:48:31
(1/NULLIF(CycleTime/3600, 0))*(Percentage/100) -- means whenever CycleTime/3600 values becomes zero, it will return NULL as output instead of Error


--
Chandu
Go to Top of Page

pattonjo
Starting Member

11 Posts

Posted - 2013-06-24 : 16:09:19
Thank you for the help!
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 00:37:29
quote:
Originally posted by pattonjo

Thank you for the help!


welcome

--
Chandu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-26 : 11:31:41
Simplify
(1/(CycleTime/3600)*(Percentage) AS NewColumn

36E * Percentage / NULLIF(CycleTime, 0) AS NewColumn



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-26 : 12:42:44
quote:
Originally posted by SwePeso

Simplify
(1/(CycleTime/3600)*(Percentage) AS NewColumn

36E * Percentage / NULLIF(CycleTime, 0) AS NewColumn



N 56°04'39.26"
E 12°55'05.63"


Whether it is a simplification depends on one's perspective. At the very least, make sure a comment describing the arithmetic is included in the code for posterity.

In doing floating point calculations, because of roundoff errors, this type of simplification can sometimes get you results that are slightly different from the original long-winded formula. In almost all cases that should not matter, and in any case you should not be relying on the precision to that extent; nonetheless, something to keep in mind.

Another thing that I have observed is that the arithmetic that you do in queries adds very little to the resource requirements. So sometimes I keep the formulas in their original form even if they can be simplified/shortened because keeping it in the long form makes the logic clear to someone looking at it.
Go to Top of Page
   

- Advertisement -