How to find the Median, 2 standard deviations, min range , max range in SQL Server 2008?
Actual Table:
=============
+-----+-----+------+-------+------------+
| Rat | Cat | A | B | C |
+-----+-----+------+-------+------------+
| AAA | 1 | 6.5 | 6.25 | 2.56 |
| AAA | 3 | 5.25 | 7.36 | 3.25 |
| AAA | 5 | 3.25 | 6.25 | 4.25 |
| AAA | 7 | 6.25 | 6.32 | 6.35 |
| AA | 1 | 7.23 | 8.52 | 7.35 |
| AA | 3 | 7.83 | 7.01 | 6.52 |
| AA | 5 | 7.2 | 7.03 | 4.52 |
| AA | 7 | 7.14 | 5.03 | 6.32 |
+-----+-----+------+-------+------------+
Expected Result:
================
--------+-------------+-------+-----+-------------+
Median | 2StanddDevi | Count | Min | Max Outlier |
--------+-------------+-------+-----+-------------+
6.25 | 4.412270768 | | | |
5.25 | 4.110490644 | | | |
4.25 | 3.055050463 | | | |
6.32 | 0.102632029 | | | |
7.35 | 1.425342064 | | | |
7.01 | 1.32378246 | | | |
7.03 | 3.001266399 | | | |
6.32 | 2.127377102 | | | |
--------+-------------+-------+-----+-------------+
The below query am using, but not able to get median value
while (Rec > 0)
begin
set Id=Id+1;
if(Id=1)
begin
set Sqlqry = 'insert into tempCal (Median) '
set Sqlqry = Sqlqry + ' SELECT cast(AVG(1.0 * '+olval+') as decimal(10,2)) AS Median from
(SELECT isnull('+colval+','''') AAA, ra = ROW_NUMBER() OVER (ORDER BY Rating, Id ),
rd = ROW_NUMBER() OVER (ORDER BY Rat DESC, Id desc) from temptbl) as x WHERE ra BETWEEN rd - 1 AND rd + 1;'
exec(Sqlqry)
end
select * from tempCal
set @Rec=@Rec-1;
end
What I have tried:
How to find the Median, 2 standard deviations, min range , max range in SQL Server 2008?
Actual Table:
=============
+-----+-----+------+-------+------------+
| Rat | Cat | A | B | C |
+-----+-----+------+-------+------------+
| AAA | 1 | 6.5 | 6.25 | 2.56 |
| AAA | 3 | 5.25 | 7.36 | 3.25 |
| AAA | 5 | 3.25 | 6.25 | 4.25 |
| AAA | 7 | 6.25 | 6.32 | 6.35 |
| AA | 1 | 7.23 | 8.52 | 7.35 |
| AA | 3 | 7.83 | 7.01 | 6.52 |
| AA | 5 | 7.2 | 7.03 | 4.52 |
| AA | 7 | 7.14 | 5.03 | 6.32 |
+-----+-----+------+-------+------------+
Expected Result:
================
--------+-------------+-------+-----+-------------+
Median | 2StanddDevi | Count | Min | Max Outlier |
--------+-------------+-------+-----+-------------+
6.25 | 4.412270768 | | | |
5.25 | 4.110490644 | | | |
4.25 | 3.055050463 | | | |
6.32 | 0.102632029 | | | |
7.35 | 1.425342064 | | | |
7.01 | 1.32378246 | | | |
7.03 | 3.001266399 | | | |
6.32 | 2.127377102 | | | |
--------+-------------+-------+-----+-------------+
The below query am using, but not able to get median value
while (Rec > 0)
begin
set Id=Id+1;
if(Id=1)
begin
set Sqlqry = 'insert into tempCal (Median) '
set Sqlqry = Sqlqry + ' SELECT cast(AVG(1.0 * '+olval+') as decimal(10,2)) AS Median from
(SELECT isnull('+colval+','''') AAA, ra = ROW_NUMBER() OVER (ORDER BY Rating, Id ),
rd = ROW_NUMBER() OVER (ORDER BY Rat DESC, Id desc) from temptbl) as x WHERE ra BETWEEN rd - 1 AND rd + 1;'
exec(Sqlqry)
end
select * from tempCal
set @Rec=@Rec-1;
end