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