MEDIAN()

Description

MEDIAN() returns the median value of a set of values, that is, returns the value in the middle after sorting a set of values. If the argument set contains an even number of values, the function returns the average of the two numbers in the middle. It can be used as an aggregate or analytical function.

Syntax

  1. > MEDIAN(expr)

Arguments

ArgumentsDescription
exprRequired. Specify the name of the array whose median value is required, whose argument type is a numeric data type or can be implicitly converted to a numeric data type.

Returned Value

The function return value and type are the same as the return value and type of its argument.

Examples

  1. mysql> select median(null);
  2. +--------------+
  3. | median(null) |
  4. +--------------+
  5. |         NULL |
  6. +--------------+
  7. 1 row in set (0.00 sec)
  9. drop table if exists t1;
  10. create table t1 (a int,b int);
  11. insert into t1 values (1,null);
  13. mysql> select median(b) from t1;
  14. +-----------+
  15. | median(b) |
  16. +-----------+
  17. |      NULL |
  18. +-----------+
  19. 1 row in set (0.01 sec)
  21. insert into t1 values (1,1);
  23. mysql> select median(b) from t1;
  24. +-----------+
  25. | median(b) |
  26. +-----------+
  27. |         1 |
  28. +-----------+
  29. 1 row in set (0.01 sec)
  31. insert into t1 values (1,2);
  33. mysql> select median(b) from t1;
  34. +-----------+
  35. | median(b) |
  36. +-----------+
  37. |       1.5 |
  38. +-----------+
  39. 1 row in set (0.01 sec)
  41. mysql> select median(b) from t1 group by a order by a;
  42. +-----------+
  43. | median(b) |
  44. +-----------+
  45. |       1.5 |
  46. +-----------+
  47. 1 row in set (0.00 sec)
  49. insert into t1 values (2,1),(2,2),(2,3),(2,4);
  51. mysql> select median(b) from t1 group by a order by a;
  52. +-----------+
  53. | median(b) |
  54. +-----------+
  55. |       1.5 |
  56. |       2.5 |
  57. +-----------+
  58. 2 rows in set (0.01 sec)