float numbers

Hi!

How do I do to make t-sql not rounding the result that i returned?

For example:
0.9616458*60 = 57,698748 (in any calculator)

while following:
----------
declare @.a float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
----------
will show :57.6987

How do I do to make MSSQL to show me the value whothout rounding it?

Thanks!Use SELECT:

select @.a*@.b

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144073554.334910.187210@.g10g2000cwb.googlegr oups.com...
Hi!

How do I do to make t-sql not rounding the result that i returned?

For example:
0.9616458*60 = 57,698748 (in any calculator)

while following:
----------
declare @.a float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
----------
will show :57.6987

How do I do to make MSSQL to show me the value whothout rounding it?

Thanks!|||Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:

declare @.x float(53)
declare @.grades int
declare @.minutes int
declare @.seconds float(53)
declare @.minwithrest float(53)
declare @.leftover float(53)

select @.x = 57.66602743
select @.grades = floor(@.x)
select @.leftover = @.x-@.grades
select @.minwithrest = (@.leftover * 60)
select @.minutes = floor(@.minwithrest)
select @.seconds = (@.minwithrest - @.minutes) * 60

print @.grades
print @.minutes
print @.seconds

gives me 57.6987 as @.seconds while it should be 57,698748

thanks!|||sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||
I ran the 2nd code you've posted and I get the 57.6987 result|||Again, use SELECT:

select @.seconds

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144077300.519079.316740@.t31g2000cwb.googlegr oups.com...
Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:

declare @.x float(53)
declare @.grades int
declare @.minutes int
declare @.seconds float(53)
declare @.minwithrest float(53)
declare @.leftover float(53)

select @.x = 57.66602743
select @.grades = floor(@.x)
select @.leftover = @.x-@.grades
select @.minwithrest = (@.leftover * 60)
select @.minutes = floor(@.minwithrest)
select @.seconds = (@.minwithrest - @.minutes) * 60

print @.grades
print @.minutes
print @.seconds

gives me 57.6987 as @.seconds while it should be 57,698748

thanks!|||At this point, I'm confused about what result you are looking for. Use
convert(decimal(X,Y),@.seconds) to fine-tune the result you need. X can
be as large as 38 places and Y is your tunable decimal size.

HTH,
Gabe|||Try:

select
cast (@.seconds as numeric (8, 6))

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144077414.697728.270000@.v46g2000cwv.googlegr oups.com...
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||I get:

57.698748

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144078109.143813.199680@.z34g2000cwc.googlegr oups.com...

I ran the 2nd code you've posted and I get the 57.6987 result|||Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||I'm on 8.00.818, which is the post-SP3a security hotfix:

http://support.microsoft.com/kb/821277

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144079629.602630.102500@.u72g2000cwu.googlegr oups.com...
Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||Something else that is puzzling me about all of this:

Put 'select @.leftover' at the end of this code.

I get:

0.66602742999999975 as the @.leftover value. Shouldn't that be
0.66602743......why did Query Analyzer do this?

If floor(@.x) = 57
and
@.Leftover = 57.66602743 - @.x then the result should be 0.66602743|||I get:

0.66602742999999975

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144080712.526308.302260@.e56g2000cwe.googlegr oups.com...
Something else that is puzzling me about all of this:

Put 'select @.leftover' at the end of this code.

I get:

0.66602742999999975 as the @.leftover value. Shouldn't that be
0.66602743......why did Query Analyzer do this?

If floor(@.x) = 57
and
@.Leftover = 57.66602743 - @.x then the result should be 0.66602743|||I can't use the convert since I don't know how many decimals to round
the result to and for this function that I'm writing the result must be
exact. This is a GPS conversion formula and if I round things always
with fir examole 6 decimals then the x,y coordinats will be wrong for
the GPS system. In my example above I need the result to be exactley
57,698748 and not 57.6987 or 57.698747999999114 ... Since I don't
know the amount of decimlas returned I cant use the convert..|||> Since I don't
> know the amount of decimlas returned I cant use the convert..

This leads to another question...what about 1 divided by 3. What will
you do with this result?

Gabe|||very interesting..

I don't know...|||(homa@.havet.se) writes:
> I can't use the convert since I don't know how many decimals to round
> the result to and for this function that I'm writing the result must be
> exact.

In such case you should not use float. Float gives you approxamite numbers.
Float values consists of a 53-bit number with a mantissa. This permits
for a broad range of value, to the price of approxamite precision.

The given example:

declare @.a float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
select @.a*@.b

Gives you 57.6987 for the print, because there is an implicit conversion
to string in SQL Server.

The SELECT statement returns a binary float value to the client, so it
up to the client how it is presented. In Query Analyzer I get
57.698748000000002, whereas in Management Studio that ships with
SQL 2005, I get 57,698748. ISQL, the command-line tool that uses
DB-Library to connect returns 57.698748.

> This is a GPS conversion formula and if I round things always
> with fir examole 6 decimals then the x,y coordinats will be wrong for
> the GPS system. In my example above I need the result to be exactley
> 57,698748 and not 57.6987 or 57.698747999999114 ... Since I don't
> know the amount of decimlas returned I cant use the convert..

If you don't know the decimals of your result before hand, you will
have to convert the result to a string, and then try to guess how many
decimals you really have.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

>

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

--

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

>

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

--

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)