# Median with GROUP BY

Suppose the following table `t1`:

```=================
|  tag  |  val  |       --+ for the sake of simplicity, val is non NULL
=================
|   a1  |  v1   |
|   a1  |  v2   |
|   a1  |  v3   |
|   a1  |  v4   |
|   a1  |  v5   |
|   a2  |  v6   |
|   a2  |  v7   |
|   a2  |  v8   |
|   a2  |  v9   |
|   ... | ...   |
=================
```

If you execute the script below in MySQL:

``````SELECT `tag`, AVG(`val`) FROM `t1` GROUP BY `tag`
``````

You would get the average values grouped by the column `tag`:

```=================
|  tag  | AVG() |
=================
|   a1  | avg1  |
|   a2  | avg2  |
|   a3  | avg3  |
|   a4  | avg4  |
|   ... |  ...  |
=================
```

Besides `AVG()`, MySQL has several other built in functions to calculate aggregate values (e.g. `SUM()`, `MAX()`, `COUNT()`, and `STD()`) that could be used in the same way as in the script aforementioned. However, there is no built in function for median.

This issue has already come up several other times at SE; however, most of them related to tables without `GROUP BY`. The only one with `GROUP BY` seems to be MySql: Count median grouped by day; however, the script seems to be overcomplicated.

### Question

What would be an easy and simple way (if possible) to calculate this median?

### Follow-up

Excellent article that complement the accepted answer:
http://danielsetzermann.com/howto/how-to-calculate-the-median-per-group-with-mysql/

``````            SELECT tag, AVG(val) as median
FROM
(
SELECT tag, val,
(SELECT count(*) FROM median t2 WHERE t2.tag = t3.tag) as ct,
seq,
(SELECT count(*) FROM median t2 WHERE t2.tag < t3.tag) as delta
FROM (SELECT tag, val, @rownum := @rownum + 1 as seq
FROM (SELECT * FROM median ORDER BY tag, val) t1
ORDER BY tag, seq
) t3 CROSS JOIN (SELECT @rownum := 0) x
HAVING (ct%2 = 0 and seq-delta between floor((ct+1)/2) and floor((ct+1)/2) +1)
or (ct%2 <> 0 and seq-delta = (ct+1)/2)
) T
GROUP BY tag
ORDER BY tag;
``````

I tried it on this dataset (mainly from here):

``````            +------+------+
| tag  | val  |
+------+------+
|    1 |    3 |
|    1 |   13 |
``````

... (see explanation below)

``````            |    3 |   12 |
|    3 |   43 |
|    3 |   15 |
+------+------+
``````

and the result was:

``````            +------+---------+
| tag  | median  |
+------+---------+
|    1 | 23.0000 |
|    2 | 22.0000 |
|    3 | 15.0000 |
+------+---------+
``````

### Explanation

Inner subqueries will be computed first: sequence is (1)(2)(3)(4).

-- (4) compute the average (of 2 lines or 1 line)

``````    SELECT tag, AVG(val) as median
FROM
(
``````

-- (3) get lines to compute the median value

``````        SELECT tag, val,
(SELECT count(*) FROM median t2                    -- +number of lines for the current tag value as ct
WHERE t2.tag = t3.tag) as ct,
seq,
(SELECT count(*) FROM median t2                    -- +number of lines before the current tag value as delta
WHERE t2.tag < t3.tag) as delta                --     to compute the starting line number of a tag
FROM (
``````

-- (2) sort dataset by tag and sequence

``````                SELECT tag, val,
@rownum := @rownum + 1 as seq       -- +@rownum enable to create a sequence from 0 by 1
FROM (
``````

-- (1) sort dataset by tag and value

``````                    SELECT * FROM median
ORDER BY tag, val) t1
``````

-- (2) continue here

``````              ORDER BY tag, seq
) t3 CROSS JOIN (SELECT @rownum := 0) x            -- +use to set @rownum to 0 (no data)
``````

-- (3) continue here

``````         HAVING (ct%2 = 0                                      -- +when ct is even, select the two lines around the middle
and seq-delta between floor((ct+1)/2)
and floor((ct+1)/2) +1)
or (ct%2 <> 0                                       -- +when ct is odd, select the one line in the middle
and seq-delta = (floor(ct+1)/2))
) T
``````

-- (4) continue here

``````      GROUP BY tag
ORDER BY tag;
``````

Dataset:

``````        after (1)     after (2)           processing (3)
+------+------+
| tag  | val  |  ct  delta  seq       seq-delta
+------+------+
|    1 |    3 |  15    0     1        1         ct : odd ct%2 <> 0
|    1 |    5 |  15    0     2        2         floor((ct+1)/2) : 8
|    1 |    7 |  15    0     3        3
|    1 |   12 |  15    0     4        4
|    1 |   13 |  15    0     5        5
|    1 |   14 |  15    0     6        6
|    1 |   21 |  15    0     7        7
|    1 |   23 |  15    0     8        8 ---> keep this line
|    1 |   23 |  15    0     9        9
|    1 |   23 |  15    0     10       10
|    1 |   23 |  15    0     11       11
|    1 |   29 |  15    0     12       12
|    1 |   39 |  15    0     13       13
|    1 |   40 |  15    0     14       14
|    1 |   56 |  15    0     15       15

|    2 |    3 |  14    15    16        1         ct : even (ct%2 = 0  )
|    2 |    5 |  14    15    17        2         floor((ct+1)/2) : 7
|    2 |    7 |  14    15    18        3         floor((ct+1)/2)+1 : 8
|    2 |   12 |  14    15    19        4
|    2 |   13 |  14    15    20        5
|    2 |   14 |  14    15    21        6
|    2 |   21 |  14    15    22        7 ---> keep this line
|    2 |   23 |  14    15    23        8 ---> keep this line
|    2 |   23 |  14    15    24        9
|    2 |   23 |  14    15    25        10
|    2 |   23 |  14    15    26        11
|    2 |   29 |  14    15    27        12
|    2 |   40 |  14    15    28        13
|    2 |   56 |  14    15    29        14

|    3 |   12 |  3     29    30        1                  ct : odd ct%2 <> 0
|    3 |   15 |  3     29    31        2 ---> keep        floor((ct+1)/2) : 2
|    3 |   43 |  3     29    32        3
+------+------+
``````

Dataset after (3)

``````    +------+------+------+------+-------+
| tag  | val  | ct   | seq  | delta |
+------+------+------+------+-------+
|    1 |   23 |   15 |    8 |     0 |
|    2 |   21 |   14 |   22 |    15 |
|    2 |   23 |   14 |   23 |    15 |
|    3 |   15 |    3 |   31 |    29 |
+------+------+------+------+-------+
``````

Outer query will compute the avg(val) group by tag value.

Hope this helps.

But what about median computing when there are null values? See EDIT2 below

### Alternative: using a function

``````    DELIMITER //
CREATE FUNCTION median(pTag int)
RETURNS real
DETERMINISTIC
BEGIN
DECLARE r real; -- result
SELECT AVG(val) INTO r
FROM
(
SELECT val,
(SELECT count(*) FROM median WHERE tag = pTag) as ct,
seq
FROM (SELECT val, @rownum := @rownum + 1 as seq
FROM (SELECT * FROM median WHERE tag = pTag ORDER BY val ) t1
ORDER BY seq
) t3
CROSS JOIN (SELECT @rownum := 0) x
HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
or (ct%2 <> 0 and seq = (ct+1)/2)
) T;
return r;
END//
DELIMITER ;
``````

But the function will be called for each row:

``````SELECT tag, median(tag) FROM median; -- my test table is 'median' too...
``````

This query will be "better":

``````select tag, median(tag)
from (select distinct tag from median) t;
``````

That's all I can do! Hope it helps!

## EDIT2 : about null values in data (column val in the example)

null values show be omited from the source data using a WHERE clause : `WHERE val IS NOT NULL`, in both 2 subqueriés that count lines and the subquery that gets data.

## EDIT3 (LAST EDIT) : change the initialisation of the @rownum position

It should by put at the deepest level : so that it declared the soonest in the excution of the query.

``````DELIMITER //
CREATE FUNCTION median(pTag int)
RETURNS real
DETERMINISTIC
BEGIN
DECLARE r real; -- result
SELECT AVG(val) INTO r
FROM
(
SELECT val,
(SELECT count(*) FROM median WHERE tag = pTag and val is not null)     as ct,
seq
FROM (SELECT val, @rownum := @rownum + 1 as seq
FROM (SELECT * FROM median
CROSS JOIN (SELECT @rownum := 0) x -- INIT @rownum here
WHERE tag = pTag and val is not null ORDER BY val
) t1
ORDER BY seq
) t3
HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
or (ct%2 <> 0 and seq = (ct+1)/2)
) T;
return r;
END//
DELIMITER ;
``````

That is the same for the query.

Test with 2 data sets more :

``````|    4 | NULL |
|    4 |   10 |
|    4 |   15 |
|    4 |   20 |
|    5 | NULL |
|    5 | NULL |
|    5 | NULL |
+------+------+
``````

39 rows in set (0.00 sec)

``````+------+--------------+
| tag  | median2(tag) |
+------+--------------+
|    1 |           23 |
|    2 |           22 |
|    3 |           15 |
|    4 |           15 |
|    5 |         NULL |
+------+--------------+
5 rows in set (0.08 sec)
``````
• Hey Patrick, what did you mean by your question about nulls? If you mean you don't know how to handle nulls correctly when calculating medians, you could just specify that if nulls do need special handling, your solution might not provide it, but please don't put an open question into your answer. Feel free to post your own follow-up question about nulls if you are interested enough and your research haven't revealed the answer you are looking for. Thank you. Dec 19 '16 at 6:57
• I think you can get rid of `ct%2 = 0` and the `OR`. All you need is something like `BETWEEN FLOOR((ct-1)/2) AND CEIL((ct-1)/2)`. For an odd `ct`, the floor and ceil will be the same. Dec 24 '16 at 5:57

Best solutions are found here.

Best approaches for grouped median

It says that there is a new PERCENTILE_CONT() function introduced in SQL Server 2012.

``````SELECT tag, MAX(Median) AS Median
FROM
(
SELECT tag,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY tag) AS Median
FROM t1
)
AS x
GROUP BY tag;
``````

Just for the records ...
Based on the accepted answer of @PatrickDezecache (with minor changes), the code I'm using at the MySQL server is the following:

``````SELECT `tag`, avg(`val`) as `median`
FROM
(
SELECT
`tag`,
`val`,
(SELECT count(*) FROM `t1` `t2` WHERE `t2`.`tag` = `t3`.`tag`) as `ct`,
`seq`,
(SELECT count(*) FROM `t1` `t2` WHERE `t2`.`tag` < `t3`.`tag`) as `delta`

FROM
(SELECT `tag`, `val`, @rownum := @rownum + 1 as `seq`
FROM (SELECT `tag`, `val` FROM `t1` ORDER BY `tag`, `val`) as `t2`
CROSS JOIN (SELECT @rownum := 0) as `x`
ORDER BY `tag`, `seq`) as `t3`
HAVING
(`ct`%2 = 0 and `seq`-`delta` between floor((`ct`+1)/2) and floor((`ct`+1)/2) +1)
or (`ct`%2 <> 0 and `seq`-`delta` = (`ct`+1)/2)
) as `t`

GROUP BY `tag`
ORDER BY `tag`;
``````

Here is an outstanding article about the question : http://danielsetzermann.com/howto/how-to-calculate-the-median-per-group-with-mysql/

• Please add here the most important bits. Otherwise this stays a link-only answer, but not long - there are chances it gets removed. Jun 28 '17 at 12:47