Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

problems with average calculations that do not give correct value

I am currently building a cube and I have two measures that I need to calculate the average for (i.e. columns measureA and measureB).

measureA is a Sum of a value and measureB is the count of the number of occurences of the value.

I define a calculated Member that employs the calculation of measureA/measureB. This gives me an average that I want to roll up.

At the lowest level the calulation is correct, however as the rollups happen the calcultion becomes invalid. At the (all) level the calculation is quite far out.

The calculated member simply gives me the (All)measureA/(All)measureB.

Can anyone point me in the direction of how I can get a calculation of the individual measureA/measureB rolled up correctly giving me an overall average?

COUNT takes into account empty cells, so when you rollup, you are considering more cells than you should.

Use AVG, or filter out empty cells when using COUNT

See

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

Hope this helps,

Santi

|||

Thanks Santi That did help me.

What I was trying to do was more complex than simply averaging a single column data and after studying the problem I found that it was far too complex and that using Avg was much better.

Many thanks

Rob