SUMSQ

Updated 2023-10-13 21:06:13.497000

Syntax

SELECT westclintech.wct.SUMSQ ( [ ALL | DISTINCT ] expression )

Description

Use the aggregate function 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.

Arguments

@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 Type

float

Remarks

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.

Examples

This example will recreate the STDEV calculation, which is simply a demonstration of how the 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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"27.4639157198435"}]}

Clearly you could have also just entered.

select stdev(test_score)

from s1;

which returns

----------------------
      27.4639157198405