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];
(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?