Results 1 to 10 of 10

Thread: STDEV function

  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: STDEV function

    Hello,

    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.


    Table1:

    Labor_Rate
    26.73
    26.73
    26.73
    26.73
    26.73

    select stdev(labor_rate) as stdev1
    from table1

    returns one row: StDev1
    0.0

    So far, so good. But!

    Table2:

    Labor_Rate
    36.53
    36.53
    36.53
    36.53
    36.53
    36.53
    36.53

    select stdev(labor_rate) as stdev1
    from table2

    returns no rows!

    This causes my stored procedure to fail when it attempts to insert the result into my table, only telling me that "a domain error occurred".

    Any ideas? Thanks!

    Gary

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Curious.

    Are the field types the same in both tables?

    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?

    blindman

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    Thank you. That is a good point. The field types are different, as I've been testing this possibility.

    Table1:

    Data Type = Decimal, Length = 9, Precision = 19, Scale = 5

    Table2:

    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.

    Thanks for reading.

    Gary

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sorry, but I can't duplicate your issue. This code works fine on my system:

    set nocount on

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stdev26]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Stdev26]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stdev36]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Stdev36]
    GO

    CREATE TABLE [dbo].[Stdev26]
    ([DecimalRate] [decimal](19, 5) NULL,
    [NumericRate] [numeric](19, 5) NULL)
    GO

    CREATE TABLE [dbo].[Stdev36]
    ([DecimalRate] [decimal](19, 5) NULL,
    [NumericRate] [numeric](19, 5) NULL)
    GO

    While (select count(*) from Stdev26) < 5 insert into Stdev26 values (26.73, 26.73)
    While (select count(*) from Stdev36) < 7 insert into Stdev36 values (36.53, 36.53)

    select STDEV(DecimalRate) as stdev1 from Stdev26
    select STDEV(NumericRate) as stdev1 from Stdev26
    select STDEV(DecimalRate) as stdev1 from Stdev36
    select STDEV(NumericRate) as stdev1 from Stdev36

    I have seen that DOMAIN ERROR message pop up on some odd occasions before, but I can't remember what the resolution was.

    Can you post some code that will duplicate your error?

    blindman

  5. #5
    Join Date
    Oct 2003
    Posts
    6
    Wow. I ran the code exactly as you posted it, and I get my same result: one row with 0 for the first two select statements, no rows for the second two select statements.

    So, keeping with this example, I get a domain error message when I try a query like this:

    insert into test_table
    select STDEV(NumericRate) as stdev1 from Stdev36

    It seems like a database problem to me. I'm running this in SQL Server 2000....maybe there is a service pack I am missing?

    Thanks

    Gary

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't hurt to check your service packs, but I don't recall seeing anything about this issue.

    Here are the results of SELECT @@Version on my system:

    Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
    Aug 6 2000 00:57:48
    Copyright (c) 1988-2000 Microsoft Corporation
    Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

    (I'm actually a service pack behind on my desktop install. There is at least a Service Pack 4 available too.)

    blindman

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Follow-up:

    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.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    blindman, i think you're running a "virgin" sql. this means NO SP's, FIXES, ETC.!!! i am sure it's not the case, is it? (8.00.760 - SP3)

  9. #9
    Join Date
    Oct 2003
    Posts
    6
    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)

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What if you try it with other aggregate functions, such as MAX, or AVERAGE?

    When it comes right down to it, I'm not so sure the statistical standard deviation of a constant series is even definable. I'll have to check my stats textbooks this evening.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •