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.
wct.SUMSQ ( [ ALL | DISTINCT ] expression )
applies the aggregate function to all values. ALL is the default.
specifies that wct.SUMSQ return the SUMSQ of unique values.
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.
Returns the summation of all expression values in the most precise expression data type.
· 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.
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
)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(*)
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.
Clearly you could have also just entered