Method for obtaining the median (center value) in SQL Server

Median refers to the data that is arranged in order of size to form a series that is located in the middle of the series. The median value is represented by Me.

According to the definition of median, half of the data studied is smaller than the median, and half is greater than the median. The median function is similar to the arithmetic mean and is also the representative value of the data studied. The median is equal to the arithmetic mean in an arithmetic or normal distribution series.

When an extreme variable value occurs in a series, it is better to use the median as the representative value than to use the arithmetic mean, because the median is not affected by the value of the extreme variable; if the purpose of the study is to reflect the intermediate level, median should also be used. Median can be used in combination to process and analyze statistical data.

The value in the center after sorting data. Divide the data into two parts. One part is greater than the value and the other part is smaller than the value. Position of the median: when the number of samples is an odd number, the median = (N + 1)/2; when the number of samples is an even number, the median is the mean of N/2 and 1 + N/2, or calculate the average of the two numbers in the middle as the median.

In Excel, the MEDIAN function is used to obtain the MEDIAN value, but this built-in function is not used in SQL Server. You need to use the following method to obtain the MEDIAN value.

1. Create Table# Tb (pidVarchar(10), coadcladconInt)
2. Insert Into# Tb
3.  Select 'Praa0370', 1Union All
4.  Select 'Praa0370', 2Union All
5.  Select 'Praa0370', 3Union All
6.  Select 'Praa0370', 4Union All
7.  Select 'Praa0370', 5Union All
8.  Select 'Praa0370', 6Union All
9.  Select 'Praa0371', 1Union All
10.  Select 'Praa0371', 2Union All
11.  Select 'Praa0371', 3Union All
12.  Select 'Praa0371', 4Union All
13.  Select 'Praa0371', 5Union All
14.  Select 'Praa0371', 6Union All
15.  Select 'Praa0371', 7
17. WITHOrdersRNAS
18. (
19. SELECTPid, coadcladcon,
20. ROW_NUMBER () OVER (PARTITIONBYPidORDER BYCoadcladcon)ASRowNum,
21. COUNT(*) OVER (PARTITIONBYPid)ASCnt
22. FROM# Tb
23. )
24. SelectPid,Avg(Coadcladcon) coadcladconFrom(
25. SELECTPid, coadcladcon, RowNum, Cnt
26. FROMOrdersRN o
27. WHERERowNumIN(Cnt + 1)/2, (Cnt + 2)/2)
28. )
29. Group ByPid