I have been reading a bit about statistics to improve the way I understand data. This is my attempt at calculating Standard Variance using data tables. I solved this simple question:
This is the code I used (I used SEDE to execute it):
CREATE TABLE #Numbers
(
Number DECIMAL(10,4),
Variance DECIMAL(10,4)
);
GO
INSERT INTO #Numbers (Number) VALUES
(23),
(37),
(45),
(49),
(56),
(63),
(63),
(70),
(72),
(82);
GO
DECLARE @Mean DECIMAL(10,4);
SET @Mean = (SELECT SUM (Number) / COUNT(Number) FROM #Numbers);
UPDATE #Numbers
SET Variance = POWER( (@Mean - Number), 2);
DECLARE @StdVariance DECIMAL(10,4);
SET @StdVariance = (SELECT SQRT( SUM(Variance) / COUNT(Number) ) FROM #Numbers);
SELECT
@Mean AS [Mean],
@StdVariance AS [Standard Variance];
Output:
(10 row(s) affected) Mean Standard Variance ------- ----------------- 56.0000 16.8701 (10 row(s) affected) (1 row(s) affected)
I'm less familiar with SQL Server than other RDBMS, so I want to make sure my use of temp tables and variables is done the "Transact-SQL Way". Is there a cleaner, smarter way to do this?