Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server deviation squared function


DEVSQ

Updated: 30 September 2010

Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the DEVSQ function


Use the multi-input aggregate DEVSQ function to calculate the sum of the squares of deviations of data points from their sample mean. The equation for deviation squared is
DEVSQ function for SQL Server 
Syntax
SELECT [wctStatistics].[wct].[DEVSQ] (
 ,<@Known_x, float,>)
Arguments
 
@Known_x
the x-values to be used in the DEVSQ calculation. @Known_x is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         DEVSQ  is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
Examples
In this example, we calculate the covariance for a single set of x-values
SELECT wct.DEVSQ(x) as DEVSQ
FROM (
      SELECT 6.1 UNION ALL
      SELECT -4.2 UNION ALL
      SELECT -10 UNION ALL
      SELECT 9.6 UNION ALL
      SELECT 9.6 UNION ALL
      SELECT 4.3 UNION ALL
      SELECT 2.3 UNION ALL
      SELECT 7.8 UNION ALL
      SELECT -7.8 UNION ALL
      SELECT 7.2
      ) n(x)
 
This produces the following result
DEVSQ
----------------------
474.469
 
(1 row(s) affected)
In this example, we will populate some temporary table with some historical financial information and then calculate the DEVSQ statistic. First, create the table and put some data in it:
CREATE TABLE #c(
      SYM         NVARCHAR(5),
      YE          BIGINT,
      REV         FLOAT,
      GPROF       FLOAT,
      OPINC       FLOAT,
      NETINC      FLOAT
      )
     
INSERT INTO #c VALUES('YHOO',2009,6460.32,3588.57,386.69,597.99)
INSERT INTO #c VALUES('YHOO',20,72.5,4185.14,12.96,418.92)
INSERT INTO #c VALUES('YHOO',2007,6969.27,4130.52,695.41,639.16)
INSERT INTO #c VALUES('YHOO',2006,6425.68,3749.96,940.97,751.39)
INSERT INTO #c VALUES('YHOO',2005,5257.67,3161.47,1107.73,1896.23)
INSERT INTO #c VALUES('GOOG',2009,23650.56,14806.45,8312.19,6520.45)
INSERT INTO #c VALUES('GOOG',20,21795.55,13174.04,5537.21,4226.86)
INSERT INTO #c VALUES('GOOG',2007,16593.99,9944.9,54.44,4203.72)
INSERT INTO #c VALUES('GOOG',2006,10604.92,6379.89,3550,3077.45)
INSERT INTO #c VALUES('GOOG',2005,6138.56,3561.47,2017.28,1465.4)
INSERT INTO #c VALUES('MSFT',2010,62484,509,24167,18760)
INSERT INTO #c VALUES('MSFT',2009,58437,46282,21225,14569)
INSERT INTO #c VALUES('MSFT',20,60420,48822,22271,17681)
INSERT INTO #c VALUES('MSFT',2007,51122,40429,18438,14065)
INSERT INTO #c VALUES('MSFT',2006,44282,36632,16064,12599)
INSERT INTO #c VALUES('ORCL',2010,26820,21056,9062,6135)
INSERT INTO #c VALUES('ORCL',2009,23252,18458,8321,5593)
INSERT INTO #c VALUES('ORCL',20,22430,17449,7844,5521)
INSERT INTO #c VALUES('ORCL',2007,17996,13805,5974,4274)
INSERT INTO #c VALUES('ORCL',2006,14380,11145,4736,3381)
INSERT INTO #c VALUES('SAP',2009,10672,6980,2588,1748)
INSERT INTO #c VALUES('SAP',20,11575,7370,2701,1847)
INSERT INTO #c VALUES('SAP',2007,10256,6631,2698,1906)
INSERT INTO #c VALUES('SAP',2006,9393,6064,2578,1871)
INSERT INTO #c VALUES('SAP',2005,8509,5460,2337,1496)
Now, calculate the DEVSQ of the revenue (REV) for each company (SYM)
SELECT #c.SYM
,wct.DEVSQ(REV) as DEVSQ
FROM #c
GROUP BY SYM
This produces the following result.
SYM   DEVSQ
----- ----------------------
GOOG 218531243.79292
MSFT 226505028
ORCL 93834243.2
SAP  5556470
YHOO 2266291.64187996
 

(5 row(s) affected)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service