How can I calculate the median of values in SQLite?

sqlite median
mafu picture mafu · Apr 2, 2013 · Viewed 21.8k times · Source

I'd like to calculate the median value in a numeric row. How can I do that in SQLite 4?

Answer

CL. picture CL. · Apr 2, 2013

Let's say that the median is the element in the middle of an ordered list.

SQLite (4 or 3) does not have any built-in function for that, but it's possible to do this by hand:

SELECT x
FROM MyTable
ORDER BY x
LIMIT 1
OFFSET (SELECT COUNT(*)
        FROM MyTable) / 2

When there is an even number of records, it is common to define the median as the average of the two middle records. In this case, the average can be computed like this:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

Combining the odd and even cases then results in this:

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2 - (SELECT COUNT(*) FROM MyTable) % 2    -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master …

Read More