Equation Error With using Parenthesis

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

SQL Server 2000 Forums

Transact-SQL (2000)

Equation Error With using Parenthesis

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-07-31 : 06:47:46

I am having trouble getting an equation to output correctly. I come from a VB background and the following would work for me there...

-> ((val1 - val2)/val2)*100

... which gives me a percentage value of val1 as part of val2.

Now I am doing the same in T-SQL using the following...

-> ((SUM(val1.CAR) - SUM(val2.CAR))/ SUM(val2.CAR))*100

And I get values of either .0 or 100!!

FYI, this sits within a CASE statement checking for zero values so there is no issue with dividing by zero.

->(SUM(val1.CAR) - SUM(val2.CAR))/ SUM(val2.CAR)

The above gave me .0 or 1 so the *100 is not an issue.

-> SUM(val1.CAR) - SUM(val2.CAR)/ SUM(val2.CAR)

The above gives me an output value but it is the vb equivalent of...

-> val1 - (val2/val2)

So there must be an issue with using the parenthesis to order the mathematical order of execution in the equation.

Can anyone let me know the T-SQL of using parenthesis?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-31 : 06:51:39

[code]((SUM(val1.CAR) - SUM(val2.CAR)) * 100.0 / SUM(val2.CAR))[/code]

KH
[spoiler]Time is always against us[/spoiler]

adjones1980
Starting Member

36 Posts

Posted - 2007-07-31 : 07:03:50

thanks for the reply. I still seem to be getting the wrong value

Val1 = 54448
val2 = 66758

The value I should be getting is -18.439

Your equation gave me 54348 written like this...

SUM(val1.CAR) - SUM(val2.CAR) * 100.0 / SUM(val2.CAR)

Hold no, just tried the following and it gave the correct result...

(SUM(val1.CAR) - SUM(val2.CAR)) * 100.0 / SUM(val2.CAR)

Thanks for your help, although I am still at a loss as to the order and use of parenthesis in T-SQL!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 07:24:54

It is exactly the same as with VB!
The difference is that dividing two integers in SQL is the same as using \ in VB.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-31 : 08:04:06

quote:
Originally posted by adjones1980

thanks for the reply. I still seem to be getting the wrong value

Val1 = 54448
val2 = 66758

The value I should be getting is -18.439

Your equation gave me 54348 written like this...

SUM(val1.CAR) - SUM(val2.CAR) * 100.0 / SUM(val2.CAR)

Hold no, just tried the following and it gave the correct result...

(SUM(val1.CAR) - SUM(val2.CAR)) * 100.0 / SUM(val2.CAR)

Thanks for your help, although I am still at a loss as to the order and use of parenthesis in T-SQL!!!

Refer
http://www.mathgoodies.com/lessons/vol7/order_operations.html

Madhivanan

Failing to plan is Planning to fail

adjones1980
Starting Member

36 Posts

Posted - 2007-07-31 : 09:07:15

quote:
Originally posted by Peso

It is exactly the same as with VB!
The difference is that dividing two integers in SQL is the same as using \ in VB.

If you see my first post, the first example is what I used in a similar VB Function and that works but when I wrote the same thing in T-SQL (the second example) and it gave me the wrong answer.

I am as surprised as you are that it didn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:16:50

I am not surprised!

It is the DATETYPE that gives "integer division" automatically! Not the "/" sign.
Using two integers and dividing them yields the same result as using "\" in VB.

What is it you don't understand?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:28:07

Step by step instructions on integer division in sql server.
Please pay attention now!

( ( SUM(val1.CAR) - SUM(val2.CAR) ) / SUM(val2.CAR) ) * 100

1) First SUM(val1.CAR) and SUM(val2.CAR) is evaluated and substracted

( -12310 / SUM(val2.CAR) ) * 100

2) Now SUM(val2.CAR) is taken from cache and divided

( -12310 / 66758 ) * 100

3) Now the division occurs because of paranthesises. BUT BEWARE! Since -12310 / 66758 = -0.1844 is less not an integer, SQL returns nearest INTEGER which is zero.

( 0 ) * 100

4) Now the multiplication occurs.

0

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:31:55

Step by step instructions on integer division in sql server.
Please pay attention now!

( 100 * ( SUM(val1.CAR) - SUM(val2.CAR) ) / SUM(val2.CAR) )

1) First SUM(val1.CAR) and SUM(val2.CAR) is evaluated and substracted

( 100 * ( -12310 ) / SUM(val2.CAR) )

2) Now SUM(val2.CAR) is taken from cache and multiplication occues since MUL has higher priority than DIV

( 100 * ( -12310 ) / 66758 )
( -1231000 / 66758 )

3) Now the division occurs.

-18

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 09:35:26

Step by step instructions on integer division in sql server.
Please pay attention now!

( 100.0 * ( SUM(val1.CAR) - SUM(val2.CAR) ) / SUM(val2.CAR) )

1) First SUM(val1.CAR) and SUM(val2.CAR) is evaluated and substracted

( 100.0 * ( -12310 ) / SUM(val2.CAR) )

2) Now SUM(val2.CAR) is taken from cache and multiplication occues since MUL has higher priority than DIV

( 100.0 * ( -12310 ) / 66758 )
( -1231000.0 / 66758 )

3) Now the division occurs. And since the division not is INTEGER anymore, decimals are present.

-18.439737559543425507055334192157

This little magic trick is call "OPERATOR PRECEDENCE" and can be read about in Books Online, the ultimate resource for understanding your tool.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources