title: Calculating distance (pythagoras) and running count in sql query
body: <p>I am trying to build a rather complicated query in SQL, and being a beginner i would immensely appreciate some help to build it.</p>
  
  <p>I am trying to achieve the following:</p>
  
  <h2><img src="https://i.stack.imgur.com/Lok6a.png" alt="population_postcodes table"></h2>
  
  <p><img src="https://i.stack.imgur.com/S9zt6.png" alt="enter image description here"></p>
  
  <p>1/ Calculate the <code>distance</code> between a postcode in the target_postcodes table - say E1 1AA - and all the postcodes in the the population_postcodes table using Cartesian latitude and longitude coordinates using Pythagoras:</p>
  
  <pre><code>SQRT( POW(MY_Y_AXIS - Y_AXIS, 2) + POW(MY_X_AXIS-X_AXIS, 2) )
  </code></pre>
  
  <p>2/ Create a new column with those <code>distance</code> values,</p>
  
  <pre><code>not sure how to do that step
  </code></pre>
  
  <p>2-bis/ Sort postcodes in the <code>population_postcodes</code> by the <code>distance</code> value we obtained,</p>
  
  <pre><code>not sure how to do that step
  </code></pre>
  
  <p>3/ Beginning with the closest postcode, add the value in the population column to a running_count column <strong>UNTIL</strong> <code>running_count</code> > <code>Number_of_beds</code> of E1 1AA,</p>
  
  <p>proposed query for running count - but missing the above breaking condition:</p>
  
  <pre><code>SELECT distance, Population,
   (SELECT sum(population_postcodes.Population)) AS Total
  
  FROM population_postcodes
  WHERE population_postcodes.distance &lt;= T1.distance) AS Total
  
  FROM population_postcodes AS T1
  </code></pre>
  
  <p>4/ Create a new table that contains the postcode E1 1AA (<code>target_postcode</code>) and the distance value of the last postcode added to our running count.</p>
  
  <p>Finally, i would need to loop this query over the whole <code>target_postcodes</code> table.</p>
  
  <p>Thank you for you very much for helping a newbie out!</p>