Calculate Standard Deviation Using SQL Server

SQL Procedure to get the Standard Deviation

Hello everyone, here is the formaula to calculate the standard deviation.

---

-- Create a temp table insert some rows as raw data.

DECLARE @tbl AS TABLE(ID INT IDENTITY(1,1) , Val DECIMAL(18,4));

INSERT INTO @tbl VALUES (10.20 );

INSERT INTO @tbl VALUES (99.27 );

INSERT INTO @tbl VALUES (23.56 );

INSERT INTO @tbl VALUES (56.92 );

INSERT INTO @tbl VALUES (02.37 );

SELECT * FROM @tbl;



-- Declare variables @AVG , @Count.

-- @AVG is the average of the "Val".

-- @Count is the number of records in the table.

DECLARE @AVG DECIMAL(18,4), @Count INT;

SELECT @AVG = AVG(VAL) , @Count = COUNT(ID) FROM @tbl;

SELECT @AVG AS Average , @Count AS RecordCount;



-- Declare the sum of Deviation

-- FORMULA = Single value is substracted by the average. The square of the result and sum of all the values will get "@SumDev".

DECLARE @SumDev DECIMAL(18,4);

SELECT @SumDev = SUM( SQUARE(VAL - @AVG) ) AS SumSquareDeviation FROM @tbl;

SELECT @SumDev;



-- Get the final stanard Deviation

-- FORMULA = Gets the last @SumDev divide it by a number less than the total number of rows(@Count - 1). Get the quareroot and get the Standard Deviation

SELECT SQRT((@SumDev /(@Count-1))) AS StandardDeviation;

---