CODEWITHSUNDEEP
sqlpostgresqlintegerwindow-functionsinteger-division
neuron
neuron

Reputation: 559

Rounding numbers to the nearest 10 in Postgres

I'm trying to solve this particular problem from PGExercises.com:

https://www.pgexercises.com/questions/aggregates/rankmembers.html

The gist of the question is that I'm given a table of club members and half hour time slots that they have booked (getting the list is a simple INNER JOIN of two tables).

I'm supposed to produce a descending ranking of members by total hours booked, rounded off to the nearest 10. I also need to produce a column with the rank, using the RANK() window function, and sort the result by the rank. (The result produces 30 records.)

The author's very elegant solution is this:

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;

Unfortunately, as a SQL newbie, my very unelegant solution is much more convoluted, using CASE WHEN and converting numbers to text in order to look at the last digit for deciding on whether to round up or down:

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;

Still, I manage to almost get it just right - out of the 30 records, I get one edge case, whose firstname is 'Ponder' and lastname is 'Stephens'. His rounded number of hours is 124.5, but the solution insists that rounding it to the nearest 10 should produce a result of 120, whilst my solution produces 130.

(By the way, there are several other examples, such as 204.5 rounding up to 210 both in mine and the exercise author's solution.)

What's wrong with my rounding logic?

Upvotes: 18

Views: 36204

Answers (5)

Oluwaseyi Amisu
Oluwaseyi Amisu

Reputation: 1

A modified version of the Author's elegant solution that works:

I hope you find it useful

select firstname, surname, round(hrs, -1) as hours, rank() over(order by 
round(hrs, -1) desc) as rank
from (select firstname, surname, sum(bks.slots) * 0.5 as hrs
from cd.members mems
inner join cd.bookings bks
on mems.memid = bks.memid
group by mems.memid) as subq
order by rank, surname, firstname;

Upvotes: 0

Bohemian
Bohemian

Reputation: 424943

To round to the nearest multiple of any number (range):

round(<value> / <range>) * <range>

“Nearest” means values exactly half way between range boundaries are rounded up.

This works for arbitrary ranges, you could round to the nearest 13 or 0.05 too if you wanted to:

round(64 / 10) * 10 —- 60
round(65 / 10) * 10 —- 70

round(19.49 / 13) * 13 -- 13
round(19.5 / 13) * 13 -- 26

round(.49 / .05) * .05 -- 0.5
round(.47 / .05) * .05 -- 0.45

Upvotes: 11

soni
soni

Reputation: 11

I don't think Bohemian's formula is correct.

The generalized formula is:

round((value + (range/2))/range) * range

so to convert to nearest 50, round((103 + 25)/50) * 50 --> will give 100

Upvotes: 1

Gajus
Gajus

Reputation: 73728

I have struggled with an equivalent issue. I needed to round number to the nearest multiple of 50. Gordon's suggestion here does not work.

My first attempt was SELECT round(120 / 50) * 50, which gives 100. However, SELECT round(130 / 50) * 50 gave 100. This is wrong; the nearest multiple is 150.

The trick is to divide using a float, e.g. SELECT round(130 / 50.0) * 50 is going to give 150.

Turns out that doing x/y, where x and y are integers, is equivalent to trunc(x/y). Where as float division correctly rounds to the nearest multiple.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269447

If you want to round to the nearest 10, then use the built-in round() function:

select round(<whatever>, -1)

The second argument can be negative, with -1 for tens, -2 for hundreds, and so on.

Upvotes: 48

Related Questions