CODEWITHSUNDEEP
sqlmedian
Daniel Chen
Daniel Chen

Reputation: 13

Finding Median Using MySQL

I know that there are many ways to find the median, but I am trying to use this method to find the median. Can someone explain to me why this does not work? The error here says "Invalid use of group function," but when I use HAVING instead of WHERE, the system doesn't recognize what RowNumber is. I'm very confused.

SELECT
    ROUND(AVG(LS.LAT_N))
FROM(
    SELECT
        LAT_N,
        ROW_NUMBER() OVER (ORDER BY LAT_N) AS RowNumber
    FROM
        STATION
    ) AS LS
WHERE
    RowNumber IN (
        IF(
            FLOOR(COUNT(LS.LAT_N)/2+0.5) = CEIL(COUNT(LS.LAT_N)/2+0.5),
            FLOOR(COUNT(LS.LAT_N)/2+0.5),
            FLOOR(COUNT(LS.LAT_N)/2+0.5) AND CEIL(COUNT(LS.LAT_N)/2+0.5)
            )

Upvotes: 1

Views: 2708

Answers (2)

Peter Dongan
Peter Dongan

Reputation: 2306

The median is the middle element in an ordered series - or the average of the two middle elements if there is an even number.

SELECT
    AVG(LAT_N)
FROM(
    SELECT
        LAT_N,
        ROW_NUMBER() OVER (ORDER BY LAT_N) AS RowNumber
    FROM
        STATION
    ) AS q
WHERE
RowNumber >= FLOOR ( (SELECT COUNT(*) FROM STATION)/2 + 0.5)
AND
RowNumber <= CEIL ( (SELECT COUNT(*) FROM STATION)/2 + 0.5)

Here is dbfiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b31e08f4ece61ecb95d9dde76c389fb1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I typically write this as:

SELECT AVG(LAT_N)
FROM (SELECT LAT_N,
             ROW_NUMBER() OVER (ORDER BY LAT_N) AS RowNumber,
             COUNT(*) OVER () as cnt
      FROM STATION
     ) s
WHERE 2 * RowNumber IN (CNT, CNT + 1, CNT + 2);

Here is a db<>fiddle.

Upvotes: 1

Related Questions