SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Performing Mathematical Calculations in Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pattonjo
Starting Member

USA
11 Posts

Posted - 06/11/2013 :  11:20:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/11/2013 :  11:37:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/12/2013 :  00:34:02  Show Profile  Reply with Quote
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

USA
11 Posts

Posted - 06/13/2013 :  00:43:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  00:48:31  Show Profile  Reply with Quote
(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

USA
11 Posts

Posted - 06/24/2013 :  16:09:19  Show Profile  Reply with Quote
Thank you for the help!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/25/2013 :  00:37:29  Show Profile  Reply with Quote
quote:
Originally posted by pattonjo

Thank you for the help!


welcome

--
Chandu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 06/26/2013 :  11:31:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 06/26/2013 :  12:42:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000