title: Rounding numbers to the nearest 10 in Postgres
body: <p>I'm trying to solve this particular problem from PGExercises.com:</p>
  
  <p><a href="https://www.pgexercises.com/questions/aggregates/rankmembers.html" rel="noreferrer">https://www.pgexercises.com/questions/aggregates/rankmembers.html</a></p>
  
  <p>The gist of the question is that I'm given a table of club members and <em>half hour</em> time slots that they have booked (getting the list is a simple INNER JOIN of two tables).</p>
  
  <p>I'm supposed to produce a descending ranking of members by <em>total hours</em> booked, rounded off to the <em>nearest 10</em>. I also need to produce a column with the rank, using the <code>RANK()</code> window function, and sort the result by the rank. (The result produces 30 records.)</p>
  
  <p>The author's very elegant solution is this:</p>
  
  <pre><code>select firstname, surname, hours, rank() over (order by hours) from
  (select firstname, surname,
  ((sum(bks.slots)+5)/20)*10 as hours
  
  from cd.bookings bks
  inner join cd.members mems
      on bks.memid = mems.memid
  group by mems.memid
  ) as subq
  order by rank, surname, firstname;
  </code></pre>
  
  <p>Unfortunately, as a SQL newbie, my very unelegant solution is much more convoluted, using <code>CASE WHEN</code> and converting numbers to text in order to look at the last digit for deciding on whether to round <em>up</em> or <em>down</em>:</p>
  
  <pre><code>SELECT
  firstname,
  surname,
  CASE 
    WHEN (SUBSTRING(ROUND(SUM(slots*0.5),0)::text from '.{1}$') IN ('5','6','7','8','9','0')) THEN CEIL(SUM(slots*0.5) /10) * 10 
    ELSE FLOOR(SUM(slots*0.5) /10) * 10 
  END AS hours,
  RANK() OVER(ORDER BY CASE 
    WHEN (SUBSTRING(ROUND(SUM(slots*0.5),0)::text from '.{1}$') IN ('5','6','7','8','9','0')) THEN CEIL(SUM(slots*0.5) /10) * 10 
    ELSE FLOOR(SUM(slots*0.5) /10) * 10 
  END DESC) as rank
  FROM cd.bookings JOIN cd.members
  ON cd.bookings.memid = cd.members.memid
  GROUP BY firstname, surname
  ORDER BY rank, surname, firstname;
  </code></pre>
  
  <p>Still, I manage to almost get it just right - out of the 30 records, I get one edge case, whose <em>firstname</em> is 'Ponder' and <em>lastname</em> is 'Stephens'. His rounded number of hours is <code>124.5</code>, but the solution insists that rounding it to the nearest 10 should produce a result of <code>120</code>, whilst my solution produces <code>130</code>.</p>
  
  <p>(By the way, there are several other examples, such as <code>204.5</code> rounding up to <code>210</code> both in mine and the exercise author's solution.)</p>
  
  <p>What's wrong with my rounding logic?</p>