Calculating Median in mysqlite

user_8117490 0
Viewed 640

I have the following table:

enter image description here

I want to calculate the median mileage per car in one query (SQLite). How do I do that?

Here's the closest I've come:

SELECT Car, Mileage, ROW_NUMBER() OVER (PARTITION BY Car ORDER BY Mileage) RowNum FROM car_table ORDER BY Car, Mileage;

My expected output is:

Car | Median Mileage

sqlite
1 Answers

You can do it with window functions ROW_NUMBER(), COUNT() and AVG():

SELECT DISTINCT Car,
       AVG(
         CASE counter % 2
           WHEN 0 THEN CASE WHEN rn IN (counter / 2, counter / 2 + 1) THEN mileage END 
           WHEN 1 THEN CASE WHEN rn = counter / 2 + 1 THEN mileage END
         END
       ) OVER (PARTITION BY car) median 
FROM (        
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY car ORDER BY mileage) rn,
         COUNT(*) OVER (PARTITION BY car) counter
  FROM car_table
)

Or:

SELECT DISTINCT Car,
       AVG(CASE WHEN rn IN (counter / 2 + 1, counter / 2 + 1 - (counter % 2 = 0)) THEN mileage END) 
       OVER (PARTITION BY car) median 
FROM (        
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY car ORDER BY mileage) rn,
         COUNT(*) OVER (PARTITION BY car) counter
  FROM car_table
)

See the demo.
Results:

Car median
Honda 11.85
Toyota 9.8
user_104988280
Related