I am writing a stored procedure that loads standard deviations into a table for records that meet certain criteria.
The problem I'm having is that sometimes the STDEV function does not return rows. In the following two cases, I would expect both queries to return one row with a value of 0. The first query does this, but the second does not.
Are you sure there aren't any null values in the second table?
I suspect that you simplified the code for the forum, but perhaps there is something else causing this problem. Could you post the entire code, or at least a large portion of it? How about the table definitions?
Thank you. That is a good point. The field types are different, as I've been testing this possibility.
Data Type = Decimal, Length = 9, Precision = 19, Scale = 5
Data Type = Numeric, Length = 9, Precision = 19, Scale = 5
However, when I switch the data in the two tables, I still get the same results...the "36.53" records return no rows.
There are no null values in either table. The entire contents of each table are posted...5 rows in Table1, 7 rows in Table2.
Also: If I add a non-36.53 record to Table2, I do get a value...so it seems to have something to do with the way SQL Server calculates standard deviations that are equal to zero.
I also wondered if it could have something to do with the way the STDEV function is defined on the server. DBAs, is this a possibility? I have tested this on three different servers, getting the same results on each.
The domain error occurs when an illegal value is passed to a function, kind of like divide by zero.
I did notice that when I ran my code with different numbers of records, for some recordcounts (6<N<11 for 26.73 values, and N>9 for 36.53 values) the result was not absolute zero, but was an approximation in scientific notation.
Try dropping the scale down to 2 digits and see what happens.
I get the same result with the scale set to 2 digits.
Different results for different numbers of records is very interesting. I find the same thing. With only 5 records in my 36.53 table, I get a standard deviation of 0. Somehow adding that sixth equivalent record and beyond stops the STDEV function from working. Stranger still, adding a sixth record to my 26.73 table did not stop that function from working.
Mathematically, I don't see any reason why the standard deviation for more than 6 identical numbers shouldn't be 0, no matter what the numbers are. Could this be a SQL Server bug with the STDEV function itself?
This is the version of SQL Server I'm using:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)