SQL Math Issue ARRRGG

[talenx]

OK maybe I have been staring at the issue for too long.. Because its HAS to be SIMPLE but I’m just not getting it...
The weird this here is when I perform the calculation within a stored procedure or query analyzer it returns a different value verses performing the same calculation in excel or the calculator.

Ok
I have two fields
COL-A = Sales
COL-B = Percent
-----------------------------
Col-A = 3000000
Col-B = 54.50
-----------------------------
Col-A / Col-B = Percent


Result when performing in excel or calculator
54.50 / 3000000 = 1.8166666666666666666666666666667

Here is the simple SQL

Select 54.50  / 3000000         
-------------- 
.0000181666

(1 row(s) affected)

Result when performing in SQL SP or Query Analyzer.
54.50 / 3000000 = .0000181666

I understand I can multiple it by 1000 to get the same result. But I really don't want to do this every time.

Any thoughts

Thanks
talenx

 

[amrita418]

Why do you think the results are different, calculater should give you:

1.81666 e-5 which is the same as .0000181666.

As mentioned in your post, if you need the output as 1.81666 then you need to multiple to result set by 100000.

Regards,
AA

 

[SQLBill]

I agree with amrita......I tried it in a calculator and got the same result (1.8166666666666667e-5).

I think you missed the e-5 in the calculator.

I even tried Excel. I entered 54.50 in A1 and 3000000 in B1 and entered the function A1/B1 in C1. My results were: 1.81667E-05.

-SQLBill

Posting advice: FAQ481-4875

 

[talenx]

amrita, sqlbill ,
Thanks for the quick response!!
Well the only reason I thought the values were different was due to the decimal placing, it was one thousands off.
in order to display the correct format I would need to multiple the value by 1000 , the issue with this is the values decimal place may not always be off by 1 thousands. It’s all based on the COL-A Sales value.
Any thoughts as to how I can control that? or am I completely missing something here?
;-)
thanks
talenx

 

[SQLBill]

But the correct value is what SQL Server showed you. Excel and calculators can't show the full value, so they show what they can and use E-xx to show how many places the decimal is off. In this case, they show E-05. Which basically means there's a "Error" in the displayed result, you need to move the decimal 5 places to the left for the true value.

-SQLBill

Posting advice: FAQ481-4875

 

[talenx]

SQLBill,

ahhhh, I get it now...
I never really paid attention to the number after the error "e" xx that makes since now. (Learn something new everyday!!!)

Now only if I could figure out how to convert the value based on the decimal places, basically perform the function that excel or the calculator does.

Thanks
Talenx

 

[SQLBill]

Check out FLOAT in the BOL.

-SQLBill

Posting advice: FAQ481-4875