HOW TO CALCULATE MEDIAN USING SQL

 The median is the value that separates the higher values from the lower values in a collection of numeric data.

In SQL, some statistical functions such as Average and Standard Deviation can be calculated using the AVG() and STDDEV() FUNCTIONS respectively.

However, the is no direct function to calculate the Median.

Below is the SQL code I used to solve a Hackerank Quiz which required me to solve for the median and round it to 4 decimal places.

1.   SELECT CAST (AVG(LAT_N) AS DECIMAL(10,4))
2.   FROM
3 . (
4.  SELECT *,ROW_NUMBER()OVER(ORDER BY LAT_N DESC) AS DESC_LAT
5.                    ROW_NUMBER()OVER(ORDER BY LAT_N ASC) AS ASC_LAT
6.  FROM STATION) AS A
7.  WHERE ASC_LAT IN (DESC_LAT,DESC_LAT +1, DESC_LAT-1)

Below is an explanation of the code

We start out by sorting the column LAT_N in Descending and Ascending order into two separate columns using the ROW_NUMBER function. (lines 4 and 5)

We need to get the values in the LAT_N column, where ASC_LAT equals DESC_LAT, the value before it, and the value after it. (line 6)

We then get the average of the 3 values. (line 1)

We round it to 4 decimal places by Casting AVG_LAT_N as a numeric value. (line 1)

We employed the use of sub-query to query the ordered LAT_N column to get the average of the 3 values that correspond to where ASC_LAT equals DESC_LAT, the value before and the value after it