How To Round With Negative Numbers?

[Bob Bryan]

I am using a select statement to obtain a result set back with aggregated
data. The problem is that I am seeing column data with 11 to 13 digits
after the decimal point. I tried using the STR function, but then the Order
By clause does not sort properly because there are negative numbers in the
aggregated data... I tried using Round, but that does no good either - it
still ends up displaying too many digits after the decimal point. Right now
I'm just using Query Analyzer to display the data, so I can live with it for
now. But, in the future, my app will be getting a result set back and I
would prefer not to have to go through each row and do a round on it from
the program. Does anyone know how to solve this problem?

Thanks for any help,

Bob

[Mark D Powell]

Bob, what is the actual problem? What does how the numbers display
have to do with the sort order? Do you want the negatvie numbers and
the positive values to sort alike? ABS function? Round in select list
but not in the order by?

Perhaps a sample SQL and output would help someone provide the right
solution.

HTH -- Mark D Powell --

[Erland Sommarskog]

Bob Bryan (Ro********************@yahoo.com) writes:

I am using a select statement to obtain a result set back with
aggregated data. The problem is that I am seeing column data with 11 to
13 digits after the decimal point. I tried using the STR function, but
then the Order By clause does not sort properly because there are
negative numbers in the aggregated data... I tried using Round, but
that does no good either - it still ends up displaying too many digits
after the decimal point. Right now I'm just using Query Analyzer to
display the data, so I can live with it for now. But, in the future, my
app will be getting a result set back and I would prefer not to have to
go through each row and do a round on it from the program. Does anyone
know how to solve this problem?

If I could understand the problem, maybe I could solve it. :-)

It sounds as if you are working with floats, which are approxamite
numbers. You can round a value, but you may still see many decimals,
because there may be no exact represenation of the number. You could
convert to decimal, which is a precise type. You could also consider
handling the formatting of the data in the client.

But without knowledge about your data and their data types it's difficult
to say anything more intelligent.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Bob Bryan]

"Mark D Powell" <Ma*********@eds.com> wrote in message
news:11********************@z14g2000cwz.googlegrou ps.com...

Bob, what is the actual problem? What does how the numbers display
have to do with the sort order? Do you want the negatvie numbers and
the positive values to sort alike? ABS function? Round in select list
but not in the order by?

Perhaps a sample SQL and output would help someone provide the right
solution.

HTH -- Mark D Powell --

Ok, here is my query:

select [TS Bars Back] as BB, [TS Bars Within] as BW,
str([TS Move %], 6, 4) as "Move %", str([TS Entry %], 6, 4) as "Entry T",
str([TS ATR Profit], 7, 2) as "P Goal", sum([P/L Comm]) as "P/L $",
str(sum(Risk), 7, 2) as "Risk",
str(avg([P/L Avg %]), 7, 2) as "P/L %",
str(sum([P/L %]), 10, 2) as "P/L % Sum",
str(avg([P/L Long]), 7, 4) as "Long $", str(avg([P/L Short]), 7, 4) as
"Short $",
Count([# of trades]) as "# Runs", Sum([# of trades]) as "# Trades",
str(sum([Max Drawdown]), 10, 2) as "Max $ DD"
from [Table1].dbo.SumResults
where Symbol = 'GE_1/1m' and [SE Time] = 3600 and [TS Entry %] = .005 and
[TS ATR Stop] = 0
group by [TS Move %], [TS Entry %], [TS Bars Within], [TS Bars Back], [TS
ATR Profit]
order by [P/L $] desc

The output looks like this:

BB BW Move % Entry T P Goal P/L $ Risk
P/L % P/L % Sum Long $ Short $ # Runs # Trades Max $ DD
30 8 0.0075 0.0050 1.20 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07
30 8 0.0075 0.0050 1.30 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07

So, you can see that the str function works well to limit the # of decimal
points displayed for the other fields.
However, if I use it for the "P/L $" field, then the sort does not come out
right because the order by sorts based
upon the resulting character string and not the number in the field. I need
to limit the number of digits displayed in
the P/L $ field without affecting the sort order. Anybody know how to do
that?

Bob

[Bob Bryan]

Thank you for the idea of using a decimal field instead of a float. Most of
my columns are floats (or reals). So, I tried doing a cast of the real
column to a decimal and it worked like a charm.

For those interested in the syntax, this is what worked:

sum(cast ([P/L Comm] as decimal(10,3))) as "P/L $",

Thanks again,

Bob

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn********************@127.0.0.1...

Bob Bryan (Ro********************@yahoo.com) writes:

I am using a select statement to obtain a result set back with
aggregated data. The problem is that I am seeing column data with 11 to
13 digits after the decimal point. I tried using the STR function, but
then the Order By clause does not sort properly because there are
negative numbers in the aggregated data... I tried using Round, but
that does no good either - it still ends up displaying too many digits
after the decimal point. Right now I'm just using Query Analyzer to
display the data, so I can live with it for now. But, in the future, my
app will be getting a result set back and I would prefer not to have to
go through each row and do a round on it from the program. Does anyone
know how to solve this problem?

If I could understand the problem, maybe I could solve it. :-)

It sounds as if you are working with floats, which are approxamite
numbers. You can round a value, but you may still see many decimals,
because there may be no exact represenation of the number. You could
convert to decimal, which is a precise type. You could also consider
handling the formatting of the data in the client.

But without knowledge about your data and their data types it's difficult
to say anything more intelligent.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp