title: How to find median given frequency of numbers?
body: <p>The <code>Numbers</code> table keeps the value of number and its frequency.</p>
  
  <pre><code>+----------+-------------+
  |  Number  |  Frequency  |
  +----------+-------------|
  |  0       |  7          |
  |  1       |  1          |
  |  2       |  3          |
  |  3       |  1          |
  +----------+-------------+
  </code></pre>
  
  <p>In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2 = 0. How to find median (output shown) given frequency of numbers?</p>
  
  <pre><code>+--------+
  | median |
  +--------|
  | 0.0000 |
  +--------+
  </code></pre>
  
  <p>I found the following solution <a href="https://github.com/kamyu104/LeetCode/blob/master/MySQL/find-median-given-frequency-of-numbers.sql" rel="nofollow noreferrer">here</a>. However, I am unable to understand it. Can someone please explain the solution and/or post a different solution with explanation?</p>
  
  <pre><code>SELECT AVG(n.Number) AS median 
  FROM Numbers n LEFT JOIN
  (
  SELECT Number, @prev := @count AS prevNumber, (@count := @count + Frequency) AS countNumber
  FROM Numbers, 
  (SELECT @count := 0, @prev := 0, @total := (SELECT SUM(Frequency) FROM Numbers)) temp ORDER BY Number
  ) n2
  ON n.Number = n2.Number
  WHERE 
  (prevNumber &lt; floor((@total+1)/2) AND countNumber &gt;= floor((@total+1)/2))
  OR
  (prevNumber &lt; floor((@total+2)/2) AND countNumber &gt;= floor((@total+2)/2))
  </code></pre>
  
  <p>Here's the SQL script for reproducibility:</p>
  
  <pre><code>CREATE TABLE `Numbers` (
    `Number` INT NULL,
    `Frequency` INT NULL);
  
  INSERT INTO `Numbers` (`Number`, `Frequency`) VALUES ('0', '7');
  INSERT INTO `Numbers` (`Number`, `Frequency`) VALUES ('1', '1');
  INSERT INTO `Numbers` (`Number`, `Frequency`) VALUES ('2', '3');
  INSERT INTO `Numbers` (`Number`, `Frequency`) VALUES ('3', '1');
  </code></pre>
  
  <p>Thanks!</p>