Post reply

Standard Deviation

  • March 15, 2004 at 12:23 am

    #152073

    Hai All,

    Is ther any way to get the Standard Deviation of numbers in a table row (i mean STDEV funtion in row wise)

    Or is it possible to Transpose a table (Rows in to columns and Columns in to Rows)

    Plese help

     

     


    subban

  • March 15, 2004 at 12:41 am

    #498707

    For your second question, search this site for crosstab. Should return some scripts that will do this.

    For your first question, can you explain a bit more what you mean?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 15, 2004 at 12:43 am

    #498709

    Create a view with union would be a solution.

  • March 15, 2004 at 1:23 am

    #498714

    Dear Frank Kalis,

    My first question what i mean is,

    See i have a table which contains details of Employee Survey,

    In the table each row represents an employeess response in the surveay

    i wan to take the STDEV of each employee ( mean STDEV of each row)

     

     

     


    subban

  • March 15, 2004 at 2:48 am

    #498719

    I think I know what you mean, you data looks something like this

    EMP    RESP1    RESP2    RESP3    RESP4

    AA     2        3        7        9

    BB     5        8        1        4

    so for AA you want the std deviation of 2, 3, 7 and 9 and for employee BB the std deviation of 5, 8 1 and 4 ?

    I think the best way to do this would be to have a seperate table, created by a stored proc that runs through the data, calculates the std deviation for each employee and puts it in the table ( stored against the employee value ), then use this table in a join back to the main results set ( via employee value ). You can find examples of how to calc std deviation on the web, but here is one way..

    find the average of the numbers ( in the case of employee AA thats 5.25 ). Deduct each value from 5.25 and square the result, e.g.

    5.25 - 2 = 3.25

    3.25 x 3.25 = 10.56

    then add up all the numbers ( for AA this is 32.75 ) divide that by the number of responses ( 4 ) = 8.1875

    then find the square root of this number, which is  2.8613. According to http://news.morningstar.com/news/ms/Investing101/riskybusinesstwo.html this should be the standard deviation fo this data. However, I ran these values against SQL server by putting them into a table and it reports the standard deviation to be 3.30 ?

     

  • March 15, 2004 at 4:06 am

    #498720

    Ok, now I got it.

    I'm not sure if I would do this in T-SQL.

    Measures of dispersions are measured as the deviations around the mean. The variance and standard deviation, which are based on squared deviations, are the two most widely used measures of dispersion. Variance is defined as the squared deviations around the mean. The standard deviation is the square root of the variance.

    Take a look at a statistical textbook (Should be explained in each beginners book on Statistics) for the formulae. Implementing this in T-SQL is likely to become a kludge. This should be job of your front-end as this is where programming languages excel at looping through a resultset.

    HTH

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 17, 2004 at 8:07 am

    #499119

    The easiest way is to create a CSV file with the Query analyzer, open an Excel spreadsheet and then create a new column at the end of the rows which used the STDEV function like

          =STDEV(a1..h1) 

    there are several standard deviation functions available so look at the EXCEL help to be sure you have the one your data needs.

     

    Ed

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply