Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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

SQL
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
Posted
Updated 1-Apr-18 22:39pm
v4
Comments
OriginalGriff 29-Mar-18 7:34am    
"its shows wrong value in sql server 2008."
So what values did you expect, what did you get, and what code did you use to get them?

Use the "Improve question" widget to edit your question and provide better information.
Patrice T 29-Mar-18 11:04am    
Show actual result and expected result for this data.
IsaiSelvan 2-Apr-18 1:31am    
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
Patrice T 2-Apr-18 1:34am    
Wrong answer. Read again my question.
IsaiSelvan 2-Apr-18 2:16am    
the below fomat result i need.

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 | | | |
--------+-------------+-------+-----+-------------+

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900