title: SQL compare multiple rows and get values based on percentage difference in same table
body: <p>I am trying to compare the buy rates from same vendorid based on % and get the sell rate if all buy values are within 2% of the minimum buyrate. I am adding 5% to that buyrate. if one buy value goes above 2% then the used row value would be zero. And if the max buy value and the min buy value is less than 2% in difference then the sell value would be (max buyrate + 5%). please see the table below that I got from following select query.</p>
  <pre><code>SELECT distinct LOWER(vendors.tag), vendors.buyrate, ((buyrate *'0.05')+buyrate) AS midrate, (select distinct
  min(midrate)) AS sellrate, vendors.ship, vendorid,  
  used FROM vendors WHERE vendors.ship IN (9,0)   
  order by vendorid
  
  </code></pre>
  <pre><code></code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>LOWER(vendors.tag)</th>
  <th>buyrate</th>
  <th>midrate</th>
  <th>sellrate</th>
  <th>ship</th>
  <th>vendorid</th>
  <th>used</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>crna</td>
  <td>1.15</td>
  <td>1.2075</td>
  <td>1.2075</td>
  <td>9</td>
  <td>2</td>
  <td>11</td>
  </tr>
  <tr>
  <td>crna</td>
  <td>1.17</td>
  <td>1.2285</td>
  <td>1.2285</td>
  <td>9</td>
  <td>2</td>
  <td>12</td>
  </tr>
  <tr>
  <td>crna</td>
  <td>1.20</td>
  <td>1.26</td>
  <td>1.26</td>
  <td>9</td>
  <td>2</td>
  <td>13</td>
  </tr>
  <tr>
  <td>prllc</td>
  <td>1.10</td>
  <td>1.155</td>
  <td>1.155</td>
  <td>9</td>
  <td>3</td>
  <td>14</td>
  </tr>
  <tr>
  <td>prllc</td>
  <td>1.16</td>
  <td>1.218</td>
  <td>1.218</td>
  <td>9</td>
  <td>3</td>
  <td>15</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.32</td>
  <td>1.386</td>
  <td>1.386</td>
  <td>9</td>
  <td>4</td>
  <td>16</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.33</td>
  <td>1.3965</td>
  <td>1.3965</td>
  <td>9</td>
  <td>4</td>
  <td>17</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.34</td>
  <td>1.407</td>
  <td>1.407</td>
  <td>9</td>
  <td>4</td>
  <td>18</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.36</td>
  <td>1.428</td>
  <td>1.428</td>
  <td>9</td>
  <td>4</td>
  <td>19</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code></code></pre>
  <p>My goal is to get the following results :</p>
  <pre><code></code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>LOWER(vendors.tag)</th>
  <th>buyrate</th>
  <th>midrate</th>
  <th>sellrate</th>
  <th>ship</th>
  <th>vendorid</th>
  <th>used</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>crna</td>
  <td>1.15</td>
  <td>1.2285</td>
  <td>1.2285</td>
  <td>9</td>
  <td>2</td>
  <td>11</td>
  </tr>
  <tr>
  <td>crna</td>
  <td>1.17</td>
  <td>1.2285</td>
  <td>1.2285</td>
  <td>9</td>
  <td>2</td>
  <td>12</td>
  </tr>
  <tr>
  <td>crna</td>
  <td>1.20</td>
  <td>1.26</td>
  <td>1.26</td>
  <td>9</td>
  <td>2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>prllc</td>
  <td>1.10</td>
  <td>1.155</td>
  <td>1.155</td>
  <td>9</td>
  <td>3</td>
  <td>14</td>
  </tr>
  <tr>
  <td>prllc</td>
  <td>1.16</td>
  <td>1.218</td>
  <td>1.218</td>
  <td>9</td>
  <td>3</td>
  <td>0</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.32</td>
  <td>1.407</td>
  <td>1.407</td>
  <td>9</td>
  <td>4</td>
  <td>16</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.33</td>
  <td>1.407</td>
  <td>1.407</td>
  <td>9</td>
  <td>4</td>
  <td>17</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.34</td>
  <td>1.407</td>
  <td>1.407</td>
  <td>9</td>
  <td>4</td>
  <td>18</td>
  </tr>
  <tr>
  <td>wmllc</td>
  <td>1.36</td>
  <td>1.428</td>
  <td>1.428</td>
  <td>9</td>
  <td>4</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code></code></pre>
  <p>Using 5.1.73 mysql version. Any help would be appreciated. Thank you.</p>