SQL Server median, standard deviation, average, and SQL Median

SQL Server median, standard deviation, average, and SQL Median

Create table examines ([e_id] [int] IDENTITY (200) not null, [dept_name] [nvarchar] () NULL, [ph_score] [int] NULL) SELECT dept_name, AVG (sp) as 'mediadata' FROM (SELECT dept_name, cast (ph_score as decimal (5, 2) sp, ROW_NUMBER () OVER (partition by dept_name order by ph_score ASC, e_id ASC) AS RowAsc, ROW_NUMBER () OVER (partition by dept_name order by ph_score DESC, e_id DESC) AS RowDesc FROM examines SOH) xWHERE RowAsc IN (RowDesc, RowDesc-1, rowDesc + 1) group by dept_nameORDER BY dept_name; select dept_name, STDEV (ph_score) as 'standard deviation 'from examines group by dept_nameselect dept_name, avg (ph_score) as 'average number' from examines group by dept_name