title: MySQL ORDER BY TOP 20 and then aggregate all remaining results in one row
body: <p>Assume the following dataset:</p>
  
  <pre><code>item    |    amount    |    total_items
  
  beer            5                10
  beans           5                15
  </code></pre>
  
  <p>...etc</p>
  
  <p>Imagine that there are 100 items. I want to <code>SELECT TOP 20 ITEMS ORDER BY AMOUNT DESC</code>. This will leave 80 items which I want to consolidate/aggregate into a single <code>item</code> row named <code>"Other"</code> where it's <code>amount</code> column will be the <code>SUM</code> of <code>amount</code>s of those 80 rows, same logic for <code>total_items</code>. This means that out of initial 100 rows, only 21 will remain.</p>
  
  <p>Can I do this in a single query? The approach I'm thinking of following is:</p>
  
  <p>1) SELECT TOP 20 and store in a #temp1 table</p>
  
  <p>2) SELECT the rest and store in a #temp2 table</p>
  
  <p>3) SELECT THE SUMS of <code>amount</code> and <code>total_items</code> from #temp2 so it becomes one row</p>
  
  <p>4) Merge the two results together</p>
  
  <p>Surely there must be a better and more efficient way of doing it.</p>