In SQL server sometimes we need to get the median of the data in the table.

Median is the middle value that lies in the set of values that we are doing the manipulation.

To get the median, we need to be able to accomplish the following:

• Sort the rows in order and find the rank for each row.

• Determine what is the "middle" rank. For example, if there are 9 rows, the middle rank would be 5.

• Obtain the value for the middle-ranked row.

So for getting the median the concept is to find the rank then find out the middle rank from the set of rank values then the median value is fetched.

Name	  Sales

Himanshu   10

Manish     15	 

Prateek    20

Mayank	   40

Suresh     50

Mohan	   20

SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales); 

Median

20