Simple way to calculate median with MySQL

Question

What s the simplest  and hopefully not too slow  way to calculate the median with MySQL  I ve used AVG x  for finding the mean  but I m having a hard time finding a simple way of calculating the median  For now  I m returning all the rows to PHP  doing a sort  and then picking the middle row  but surely there must be some simple way of doing it in a single MySQL query   Example data   id   val --------  1    4  2    7  3    2  4    2  5    9  6    8  7    3   Sorting on val gives 2 2 3 4 7 8 9  so the median should be 4  versus SELECT AVG val  which    5

User · Answer

In some cases median gets calculated as follows :

The "median" is the "middle" value in the list of numbers when they are ordered by value. For even count sets, median is average of the two middle values. I've created a simple code for that :

$midValue = 0;
$rowCount = "SELECT count(*) as count {$from} {$where}";

$even = FALSE;
$offset = 1;
$medianRow = floor($rowCount / 2);
if ($rowCount % 2 == 0 && !empty($medianRow)) {
  $even = TRUE;
  $offset++;
  $medianRow--;
}

$medianValue = "SELECT column as median 
               {$fromClause} {$whereClause} 
               ORDER BY median 
               LIMIT {$medianRow},{$offset}";

$medianValDAO = db_query($medianValue);
while ($medianValDAO->fetch()) {
  if ($even) {
    $midValue = $midValue + $medianValDAO->median;
  }
  else {
    $median = $medianValDAO->median;
  }
}
if ($even) {
  $median = $midValue / 2;
}
return $median;

The $median returned would be the required result :-)

User · Answer

If MySQL has ROW NUMBER  then the MEDIAN is  be inspired by this SQL Server query    WITH Numbered AS    SELECT    COUNT    OVER    AS Cnt      ROW NUMBER   OVER  ORDER BY val  AS RowNum FROM yourtable   SELECT id  val FROM Numbered WHERE RowNum IN   Cnt 1  2   Cnt 2  2      The IN is used in case you have an even number of entries   If you want to find the median per group  then just PARTITION BY group in your OVER clauses   Rob

User · Answer

In MariaDB   MySQL   SELECT AVG dd val  as median val FROM   SELECT d val   rownum   rownum 1 as  row number    total rows   rownum   FROM data d   SELECT  rownum  0  r   WHERE d val is NOT NULL   -- put some where clause here   ORDER BY d val   as dd WHERE dd row number IN   FLOOR   total rows 1  2   FLOOR   total rows 2  2       Steve Cohen points out  that after the first pass   rownum will contain the total number of rows   This can be used to determine the median  so no second pass or join is needed   Also AVG dd val  and dd row number IN      is used to correctly produce a median when there are an even number of records   Reasoning   SELECT FLOOR  3 1  2  FLOOR  3 2  2   -- when total rows is 3  avg rows 2 and 2 SELECT FLOOR  4 1  2  FLOOR  4 2  2   -- when total rows is 4  avg rows 2 and 3   Finally  MariaDB 10 3 3  contains a MEDIAN function

User · Answer

My code  efficient without tables or additional variables   SELECT   SUBSTRING INDEX SUBSTRING INDEX group concat val order by val        floor 1   count val -1    2          -1      SUBSTRING INDEX SUBSTRING INDEX group concat val order by val        ceiling 1   count val -1    2          -1    2 as median FROM table

User · Answer

This way seems include both even and odd count without subquery   SELECT AVG t1 x  FROM table t1  table t2 GROUP BY t1 x HAVING SUM SIGN t1 x - t2 x     0

User · Answer

After reading all previous ones they didn t match with my actual requirement so I implemented my own one which doesn t need any procedure or complicate statements  just I GROUP CONCAT all values from the column I wanted to obtain the MEDIAN and applying a COUNT DIV BY 2 I extract the value in from the middle of the list like the following query does      POS is the name of the column I want to get its median    query  SELECT SUBSTRING INDEX       SUBSTRING INDEX           GROUP CONCAT pos ORDER BY CAST pos AS SIGNED INTEGER  desc SEPARATOR                  COUNT    2           -1   AS  pos med  FROM table name GROUP BY any criterial   I hope this could be useful for someone in the way many of other comments were for me from this website

User · Answer

Another riff on Velcrow s answer  but uses a single intermediate table and takes advantage of the variable used for row numbering to get the count  rather than performing an extra query to calculate it   Also starts the count so that the first row is row 0 to allow simply using Floor and Ceil to select the median row s    SELECT Avg tmp val  as median val     FROM  SELECT inTab val   rows     rows   1 as rowNum               FROM data as inTab    SELECT  rows    -1  as init               -- Replace with better where clause or delete               WHERE 2  gt  1               ORDER BY inTab val  as tmp     WHERE tmp rowNum in  Floor  rows   2   Ceil  rows   2

User · Answer

Taken from  http   mdb-blog blogspot com 2015 06 mysql-find-median-nth-element-without html  I would suggest another way  without join   but working with strings  i did not checked it with tables with large data   but small medium tables it works just fine   The good thing here  that it works also by GROUPING so it can return the median for several items   here is test code for test table   DROP TABLE test test median CREATE TABLE test test median AS SELECT  book  AS grp  4 AS val UNION ALL SELECT  book   7 UNION ALL SELECT  book   2 UNION ALL SELECT  book   2 UNION ALL SELECT  book   9 UNION ALL SELECT  book   8 UNION ALL SELECT  book   3 UNION ALL  SELECT  note   11 UNION ALL  SELECT  bike   22 UNION ALL SELECT  bike   26    and the code for finding the median for each group   SELECT grp           SUBSTRING INDEX  SUBSTRING INDEX  GROUP CONCAT val ORDER BY val        COUNT    2         -1  as the median           GROUP CONCAT val ORDER BY val  as all vals for debug FROM test test median GROUP BY grp   Output   grp   the median  all vals for debug bike  22          22 26 book  4           2 2 3 4 7 8 9 note  11          11

User · Answer

Often  we may need to calculate Median not just for the whole table  but for aggregates with respect to our ID  In other words  calculate median for each ID in our table  where each ID has many records   good performance and works in many SQL   fixes problem of even and odds  more about performance of different Median-methods https   sqlperformance com 2012 08 t-sql-queries median    SELECT our id  AVG 1 0   our val  as Median FROM   SELECT our id  our val     COUNT    OVER  PARTITION BY our id  AS cnt    ROW NUMBER   OVER  PARTITION BY our id ORDER BY our val  AS rn   FROM our table   AS x WHERE rn IN   cnt   1  2   cnt   2  2  GROUP BY our id    Hope it helps

User · Answer

Optionally  you could also do this in a stored procedure   DROP PROCEDURE IF EXISTS median  DELIMITER    CREATE PROCEDURE median  table name VARCHAR 255   column name VARCHAR 255   where clause VARCHAR 255   BEGIN   -- Set default parameters   IF where clause IS NULL OR where clause      THEN     SET where clause   1    END IF     -- Prepare statement   SET  sql   CONCAT       SELECT AVG middle values  AS  median  FROM         SELECT t1    column name    AS  middle values  FROM                     SELECT  row   row 1 as  row   x    column name              FROM    table name   AS x   SELECT  row  0  AS r           WHERE    where clause    ORDER BY x    column name              AS t1                      SELECT COUNT    as  count            FROM    table name    x           WHERE    where clause              AS t2         -- the following condition will return 1 record for odd number sets  or 2 records for even number sets          WHERE t1 row  gt   t2 count 2           AND t1 row  lt     t2 count 2  1   AS t3            -- Execute statement   PREPARE stmt FROM  sql    EXECUTE stmt  END   DELIMITER     -- Sample usage  -- median table name  column name  where condition   CALL median  products    price   NULL

User · Answer

I found the accepted solution didn t work on my MySQL install  returning an empty set  but this query worked for me in all situations that I tested it on   SELECT x val from data x  data y GROUP BY x val HAVING SUM SIGN 1-SIGN y val-x val    COUNT     gt   5 LIMIT 1

User · Answer

I have this below code which I found on HackerRank and it is pretty simple and works in each and every case   SELECT M MEDIAN COL FROM MEDIAN TABLE M WHERE      SELECT COUNT MEDIAN COL  FROM MEDIAN TABLE WHERE MEDIAN COL  lt  M MEDIAN COL         SELECT COUNT MEDIAN COL  FROM MEDIAN TABLE WHERE MEDIAN COL  gt  M MEDIAN COL

User · Answer

Most of the solutions above work only for one field of the table  you might need to get the median  50th percentile  for many fields on the query     I use this    SELECT CAST SUBSTRING INDEX SUBSTRING INDEX   GROUP CONCAT field name ORDER BY field name SEPARATOR              50 100   COUNT      1        -1  AS DECIMAL  AS  Median  FROM table name    You can replace the  50  in example above to any percentile  is very efficient   Just make sure you have enough memory for the GROUP CONCAT  you can change it with   SET group concat max len   10485760   10MB max length   More details  http   web performancerasta com metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query

User · Answer

Based on  bob s answer  this generalizes the query to have the ability to return multiple medians  grouped by some criteria   Think  e g   median sale price for used cars in a car lot  grouped by year-month   SELECT      period       AVG middle values  AS  median   FROM       SELECT t1 sale price AS  middle values   t1 row num  t1 period  t2 count     FROM           SELECT               last period   period AS  last period                period  DATE FORMAT sale date    Y- m   AS  period               IF   period lt  gt  last period   row  1   row   row 1  as  row num                x sale price           FROM listings AS x   SELECT  row  0  AS r           WHERE 1             -- where criteria goes here           ORDER BY DATE FORMAT sale date    Y m    x sale price           AS t1     LEFT JOIN               SELECT COUNT    as  count   DATE FORMAT sale date    Y- m   AS  period            FROM listings x           WHERE 1             -- same where criteria goes here           GROUP BY DATE FORMAT sale date    Y m             AS t2         ON t1 period   t2 period       AS t3 WHERE      row num  gt    count 2       AND row num  lt     count 2    1  GROUP BY t3 period ORDER BY t3 period

User · Answer

Takes care about an odd value count - gives the avg of the two values in the middle in that case   SELECT AVG val  FROM     SELECT x id  x val from data x  data y       GROUP BY x id  x val       HAVING SUM SIGN 1-SIGN IF y val-x val 0 AND x id    y id  SIGN x id-y id   y val-x val     IN  ROUND  COUNT     2   ROUND  COUNT    1  2       sq

User · Answer

I found this answer very helpful - https   www eversql com how-to-calculate-median-value-in-mysql-using-a-simple-sql-query   SET  rowindex    -1   SELECT    AVG g grade  FROM     SELECT  rowindex   rowindex   1 AS rowindex         grades grade AS grade     FROM grades     ORDER BY grades grade  AS g WHERE g rowindex IN  FLOOR  rowindex   2    CEIL  rowindex   2

User · Answer

Install and use this mysql statistical functions  http   www xarg org 2012 07 statistical-functions-in-mysql   After that  calculate median is easy   SELECT median val  FROM data

User · Answer

Building off of velcro s answer  for those of you having to do a median off of something that is grouped by another parameter    SELECT grp field  t1 val FROM      SELECT grp field   rownum  IF  s   grp field   rownum   1  0  AS row number      s  IF  s   grp field   s  grp field  AS sec  d val   FROM data d    SELECT  rownum  0   s  0  r   ORDER BY grp field  d val   as t1 JOIN     SELECT grp field  count    as total rows   FROM data d   GROUP BY grp field   as t2 ON t1 grp field   t2 grp field WHERE t1 row number floor total rows 2  1

User · Answer

Knowing exact row count you can use this query   SELECT  lt value gt  AS VAL FROM  lt table gt  ORDER BY VAL LIMIT 1 OFFSET  lt half gt    Where  lt half gt    ceiling  lt size gt    2 0  - 1

User · Answer

I used a two query approach    first one to get count  min  max and avg second one  prepared statement  with a  LIMIT  count 2  1  and  ORDER BY     clauses to get the median value   These are wrapped in a function defn  so all values can be returned from one call   If your ranges are static and your data does not change often  it might be more efficient to precompute store these values and use the stored values instead of querying from scratch every time

User · Answer

A comment on this page in the MySQL documentation has the following suggestion   --  mostly  High Performance scaling MEDIAN function per group -- Median defined in http   en wikipedia org wiki Median -- -- by Peter Hlavac -- 06 11 2008 -- -- Example Table   DROP table if exists table median  CREATE TABLE table median  id INTEGER 11  val INTEGER 11    COMMIT    INSERT INTO table median  id  val  VALUES  1  7    1  4    1  5    1  1    1  8    1  3    1  6    2  4    3  5    3  2    4  5    4  12    4  1    4  7      -- Calculating the MEDIAN SELECT  a    0  SELECT id  AVG val  AS MEDIAN FROM   SELECT id  val FROM   SELECT -- Create an index n for every id  a      a   1  mod o c AS shifted n  IF  a mod o c 0  o c   a  AS n  o id  o val  -- the number of elements for every id o c FROM   SELECT t o id  val  c FROM table median t o INNER JOIN  SELECT id  COUNT 1  AS c FROM table median GROUP BY id   t2 ON  t2 id   t o id  ORDER BY t o id val   o   a WHERE IF  -- if there is an even number of elements -- take the lower and the upper median -- and use AVG lower upper  c MOD 2   0  n   c DIV 2 OR n    c DIV 2  1   -- if its an odd number of elements -- take the first if its only one element -- or take the one in the middle IF  c   1  n   1  n   c DIV 2   1       a GROUP BY id   -- Explanation  -- The Statement creates a helper table like -- -- n id val count -- ---------------- -- 1  1  1  7 -- 2  1  3  7 -- 3  1  4  7 -- 4  1  5  7 -- 5  1  6  7 -- 6  1  7  7 -- 7  1  8  7 -- -- 1  2  4  1  -- 1  3  2  2 -- 2  3  5  2 -- -- 1  4  1  4 -- 2  4  5  4 -- 3  4  7  4 -- 4  4  12  4   -- from there we can select the n-th element on the position  count div 2   1

User · Answer

You could use the user-defined function that s found here

User · Answer

I propose a faster way   Get the row count   SELECT CEIL COUNT    2  FROM data   Then take the middle value in a sorted subquery   SELECT max val  FROM  SELECT val FROM data ORDER BY val limit  middlevalue  x   I tested this with a 5x10e6 dataset of random numbers and it will find the median in under 10 seconds

User · Answer

Medians grouped by dimension   SELECT your dimension  avg t1 val  as median val FROM   SELECT  rownum   rownum 1 AS  row number      IF  dim  lt  gt  d your dimension   rownum    0  NULL       dim    d your dimension AS your dimension     d val    FROM data d    SELECT  rownum  0  r   SELECT  dim     something unreal   d   WHERE 1   -- put some where clause here   ORDER BY d your dimension  d val   as t1 INNER JOIN       SELECT d your dimension      count    as total rows   FROM data d   WHERE 1   -- put same where clause here   GROUP BY d your dimension   as t2 USING your dimension  WHERE 1 AND t1 row number in   floor  total rows 1  2   floor  total rows 2  2     GROUP BY your dimension

User · Answer

I just found another answer online in the comments      For medians in almost any SQL   SELECT x val from data x  data y GROUP BY x val HAVING SUM SIGN 1-SIGN y val-x val       COUNT    1  2    Make sure your columns are well indexed and the index is used for filtering and sorting  Verify with the explain plans   select count    from table --find the number of rows   Calculate the  median  row number  Maybe use  median row   floor count   2    Then pick it out of the list   select val from table order by val asc limit median row 1   This should return you one row with just the value you want   Jacob

User · Answer

The below query will work perfect for both even or odd number of rows  In the subquery  we are finding the value s  which has same number of rows before and after it  In case of odd rows the having clause will evaluate to 0  same number of rows before and after cancels out the sign   Similarly  for even rows the having clause evaluates to 1 for two rows  the center 2 rows  because they will  collectively  have same number of rows before and after  In the outer query  we will avg out either the single value  in case of odd rows  or  2 values in case of even rows   select avg val  as median from       select d1 val     from data d1 cross join data d2     group by d1 val     having abs sum sign d1 val-d2 val    in  0 1    sub  Note  In case your table has duplicate values  the above having clause should be changed to the below condition  In this case  there could be values outside of the original possibilities of 0 1  The below condition will make this condition dynamic and work in case of duplicates too  having sum case when d1 val d2 val then 1 else 0 end  gt   abs sum sign d1 val-d2 val

User · Answer

as i just needed a median AND percentile solution  I made a simple and quite flexible function based on the findings in this thread  I know that I am happy myself if I find  readymade  functions that are easy to include in my projects  so I decided to quickly share   function mysql percentile  table   column   where   percentile   0 5          sql                 SELECT  t1      column    as  percentile  FROM               SELECT  rownum   rownum 1 as  row number    d      column                  FROM     table     d     SELECT  rownum  0   r                   where                 ORDER BY  d      column                  as  t1                                SELECT count    as  total rows                FROM     table     d                   where                 as  t2              WHERE 1             AND  t1   row number  floor  total rows       percentile    1                   result   sql  sql  1        if   empty  result             return  result  percentile                 else           return 0             Usage is very easy  example from my current project        table   DBPRE  zip    slug   column    seconds    where    WHERE  reached     1  AND  time   gt       start time            reaching  median     mysql percentile  table   column   where  0 5        reaching  percentile25     mysql percentile  table   column   where  0 25        reaching  percentile75     mysql percentile  table   column   where  0 75

User · Answer

set  r   0   select       case when mod c 2  0 then round sum lat N  4      else round sum lat N  2 4       end as Med   from       select lat N   r     r 1   r as id from station order by lat N  A     cross join      select  count 1  1  2 as c from station  B where id  gt   floor c  and id  lt  ceil c

User · Answer

Single query to archive the perfect median   SELECT  COUNT    as total rows   IF count    2   1  CAST SUBSTRING INDEX SUBSTRING INDEX  GROUP CONCAT val ORDER BY val SEPARATOR            50 100   COUNT           -1  AS DECIMAL   ROUND  CAST SUBSTRING INDEX SUBSTRING INDEX  GROUP CONCAT val ORDER BY val SEPARATOR            50 100   COUNT      1        -1  AS DECIMAL    CAST SUBSTRING INDEX SUBSTRING INDEX  GROUP CONCAT val ORDER BY val SEPARATOR            50 100   COUNT           -1  AS DECIMAL     2   as median   AVG val  as average  FROM  data

User · Answer

SELECT      SUBSTRING INDEX          SUBSTRING INDEX              GROUP CONCAT field ORDER BY field                                                   ROUND                      LENGTH GROUP CONCAT field   -                      LENGTH                          REPLACE                              GROUP CONCAT field                                                                                                                                       2    1                                              -1           FROM     table   The above seems to work for me

User · Answer

Here is my way   Of course  you could put it into a procedure  -   SET  median counter    SELECT FLOOR COUNT    2  - 1 AS  median counter  FROM  data     SET  median   CONCAT  SELECT  val  FROM  data  ORDER BY  val  LIMIT     median counter     1     PREPARE median FROM  median   EXECUTE median    You could avoid the variable  median counter  if you substitude it   SET  median   CONCAT   SELECT  val  FROM  data  ORDER BY  val  LIMIT                           SELECT FLOOR COUNT    2  - 1 AS  median counter  FROM  data                             1                          PREPARE median FROM  median   EXECUTE median

User · Answer

The following SQL Code will help you to calculate the median in MySQL using user defined variables    x000D   x000D  create table employees salary int   x000D   x000D  insert into employees values 8   x000D  insert into employees values 23   x000D  insert into employees values 45   x000D  insert into employees values 123   x000D  insert into employees values 93   x000D  insert into employees values 2342   x000D  insert into employees values 2238   x000D   x000D  select   from employees  x000D   x000D  Select salary from employees  order by salary  x000D   x000D  set  rowid 0  x000D  set  cnt  select count    from employees   x000D  set  middle no ceil  cnt 2   x000D  set  odd even null  x000D   x000D  select AVG salary  from  x000D   select salary  rowid   rowid 1 as rid   CASE WHEN mod  cnt 2  0  THEN  odd even  1 ELSE  odd even  0 END  as odd even status  from employees  order by salary  as tbl where tbl rid  middle no or tbl rid   middle no  odd even   x000D   x000D   x000D    If you are looking for detailed explanation  please refer this blog

User · Answer

Unfortunately  neither TheJacobTaylor s nor velcrow s answers return accurate results for current versions of MySQL   Velcro s answer from above is close  but it does not calculate correctly for result sets with an even number of rows   Medians are defined as either 1  the middle number on odd numbered sets  or 2  the average of the two middle numbers on even number sets   So  here s velcro s solution patched to handle both odd and even number sets   SELECT AVG middle values  AS  median  FROM     SELECT t1 median column AS  middle values  FROM             SELECT  row   row 1 as  row   x median column       FROM median table AS x   SELECT  row  0  AS r       WHERE 1       -- put some where clause here       ORDER BY x median column       AS t1              SELECT COUNT    as  count        FROM median table x       WHERE 1       -- put same where clause here       AS t2     -- the following condition will return 1 record for odd number sets  or 2 records for even number sets      WHERE t1 row  gt   t2 count 2 and t1 row  lt     t2 count 2   1   AS t3    To use this  follow these 3 easy steps    Replace  median table   2 occurrences  in the above code with the name of your table Replace  median column   3 occurrences  with the column name you d like to find a median for If you have a WHERE condition  replace  WHERE 1   2 occurrences  with your where condition

User · Answer

I have a database containing about 1 billion rows that we require to determine the median age in the set   Sorting a billion rows is hard  but if you aggregate the distinct values that can be found  ages range from 0 to 100   you can sort THIS list  and use some arithmetic magic to find any percentile you want as follows   with rawData count value  as       select p YEAR OF BIRTH         from dbo PERSON p    overallStats  avg value  stdev value  min value  max value  total  as     select avg 1 0   count value  as avg value      stdev count value  as stdev value      min count value  as min value      max count value  as max value      count    as total   from rawData    aggData  count value  total  accumulated  as     select count value       count    as total           SUM count     OVER  ORDER BY count value ROWS UNBOUNDED PRECEDING  as accumulated   FROM rawData   group by count value   select o total as count value    o min value      o max value      o avg value      o stdev value      MIN case when d accumulated  gt    50   o total then count value else o max value end  as median value      MIN case when d accumulated  gt    10   o total then count value else o max value end  as p10 value      MIN case when d accumulated  gt    25   o total then count value else o max value end  as p25 value      MIN case when d accumulated  gt    75   o total then count value else o max value end  as p75 value      MIN case when d accumulated  gt    90   o total then count value else o max value end  as p90 value from aggData d cross apply overallStats o GROUP BY o total  o min value  o max value  o avg value  o stdev value     This query depends on your db supporting window functions  including ROWS UNBOUNDED PRECEDING  but if you do not have that it is a simple matter to join aggData CTE with itself and aggregate all prior totals into the  accumulated  column which is used to determine which value contains the specified precentile  The above sample calcuates p10  p25  p50  median   p75  and p90   -Chris

User · Answer

create table med id integer   insert into med id  values 1   insert into med id  values 2   insert into med id  values 3   insert into med id  values 4   insert into med id  values 5   insert into med id  values 6    select  MIN count  MAX count   2 from   select case when  select count    from  med A where A id lt B id   select count    2 from med  OR   select count    from med A where A id gt B id   select count    2  from med  then cast B id as float end as count from med B  C     column   ----------   3 5  1 row    OR  select cast avg id  as float  from   select t1 id from med t1 JOIN med t2 on t1 id   t2 id  group by t1 id having ABS SUM SIGN t1 id-t2 id    1  A

User · Answer

MySQL has supported window functions since version 8 0  you can use ROW NUMBER or DENSE RANK  DO NOT use RANK as it assigns the same rank to same values  like in sports ranking     SELECT AVG t1 val  AS median val   FROM  SELECT val                  ROW NUMBER   OVER ORDER BY val  AS rownum           FROM data  t1          SELECT COUNT    AS num records FROM data  t2  WHERE t1 row num IN         FLOOR  t2 num records   1    2            FLOOR  t2 num records   2    2

User · Answer

My solution presented below works in just one query without creation of table  variable or even sub-query  Plus  it allows you to get median for each group in group-by queries  this is what i needed      SELECT  columnA    SUBSTRING INDEX SUBSTRING INDEX GROUP CONCAT  columnB  ORDER BY  columnB         CEILING  COUNT  columnB   2          -1  medianOfColumnB FROM  tableC  -- some where clause if you want GROUP BY  columnA     It works because of a smart use of group concat and substring index   But  to allow big group concat  you have to set group concat max len to a higher value  1024 char by default   You can set it like that  for current sql session      SET SESSION group concat max len   10000   -- up to 4294967295 in 32-bits platform    More infos for group concat max len  https   dev mysql com doc refman 5 1 en server-system-variables html sysvar group concat max len

User · Answer

Try something like   SELECT   CAST  AVG val  AS DECIMAL 10 4   FROM       SELECT      val      ROW NUMBER   OVER  ORDER BY val   -1 AS rn      COUNT 1  OVER    -1 AS cnt     FROM STATION   as tmp WHERE rn IN  FLOOR cnt 2  CEILING  cnt 2        Note   The reason for -1 is to make it zero indexed  i e row number now starts from 0 instead of 1

User · Answer

These methods select from the same table twice  If the source data are coming from an expensive query  this is a way to avoid running it twice   select KEY FIELD  AVG VALUE FIELD  MEDIAN VALUE from       select KEY FIELD  VALUE FIELD  RANKF        rownumr    IF  prevrowidr KEY FIELD  rownumr 1 1  RANKR        prevrowidr    KEY FIELD     FROM           SELECT KEY FIELD  VALUE FIELD  RANKF         FROM               SELECT KEY FIELD  VALUE FIELD                 rownumf    IF  prevrowidf KEY FIELD  rownumf 1 1  RANKF                prevrowidf    KEY FIELD                  FROM                   SELECT KEY FIELD  VALUE FIELD                  FROM                       -- some expensive query                     B                 ORDER BY  KEY FIELD  VALUE FIELD               C                SELECT  rownumf    1  t rownum                SELECT  prevrowidf         t previd           D         ORDER BY  KEY FIELD  RANKF DESC       E        SELECT  rownumr    1  t rownum        SELECT  prevrowidr         t previd   F WHERE RANKF-RANKR BETWEEN -1 and 1 GROUP BY KEY FIELD

User · Answer

A simple way to calculate Median in MySQL set  ct     select count 1  from station   set  row    0   select avg a val  as median from   select   from  table order by val  a where  select  row     row   1  between  ct 2 0 and  ct 2 0  1

[sql] Simple way to calculate median with MySQL

Examples related to sql

Examples related to mysql

Examples related to statistics

Examples related to median