SUMSQ
Updated: 17 August 2010
Use SUMSQ to calculate the sum of the squares of all the values, or only the DISTINCT values, in the expression. SUMSQ can be used with numeric columns only. Null values are ignored. May be followed by the OVER clause.
Syntax
wct.SUMSQ ( [ ALL | DISTINCT ] expression )
Arguments
ALL
applies the aggregate function to all values. ALL is the default.
DISTINCT
specifies that wct.SUMSQ return the SUMSQ of unique values.
expression
is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.
Return Types
Returns the summation of all expression values in the most precise expression data type.
Remarks
· Distinct aggregates are not supported when CUBE or ROLLUP are used. If used, the SQL Server 2005 Database Engine returns an error message and cancels the query.
Example
This example will recreate the STDEV function, which is simply a demonstration of how the wct.SUMSQ function works. Obviously, this would not be a replacement for the STDEV function.
Create the following table, which is holding test scores for some population.
CREATE TABLE [dbo].[s1](
[ID] [float] NOT NULL,
[test_score] [float] NOT NULL,
CONSTRAINT [PK_s1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert date into the table that was just created.
insert into s1 values (1,1299)
insert into s1 values (2,1301)
insert into s1 values (3,1303)
insert into s1 values (4,1310)
insert into s1 values (5,1318)
insert into s1 values (6,1322)
insert into s1 values (7,1345)
insert into s1 values (8,1350)
insert into s1 values (9,1368)
insert into s1 values (10,1370)
Run the following SELECT statement.
;WITH CTE_Average (test_avg, num_tests) AS
(
select avg(test_score), count(*)
from s1
)
SELECT SQRT(wct.SUMSQ(s1.test_score - test_avg)/(num_tests -1))
from s1, CTE_Average
group by CTE_Average.num_tests
Here is the result set.
----------------------
27.4639157198435
Clearly you could have also just entered
select stdev(test_score)
from s1
which returns
----------------------
27.4639157198405