Often in PostgreSQL you may want to round the numbers in a column to the nearest 10.
You can use the ROUND function with the following syntax to do so:
SELECT ROUND(points, -1) AS round_points
FROM athletes;
This particular example selects each of the values in the points column of the table named athletes, rounded to the nearest 10. The result is saved in a new column named round_points.
Note that the first argument in the ROUND function specifies the column to round and the second argument specifies the number of decimal places to round to.
By specifying a value of -1, we are able to round each value in the column to the nearest 10.
The following example shows how to use this syntax in practice.
Example: Round Numbers to Nearest 10 in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points NUMERIC
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22.557);
INSERT INTO athletes VALUES (0002, 'Celtics', 30.347);
INSERT INTO athletes VALUES (0003, 'Kings', 12.322);
INSERT INTO athletes VALUES (0004, 'Kings', 25.918);
INSERT INTO athletes VALUES (0005, 'Mavs', 31.14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16.34);
INSERT INTO athletes VALUES (0007, 'Celtics', 10.596);
INSERT INTO athletes VALUES (0008, 'Celtics', 14.007);
INSERT INTO athletes VALUES (0009, 'Kings', 20.654);
INSERT INTO athletes VALUES (0010, 'Celtics', 26.328);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points
-----------+---------+--------
1 | Mavs | 22.557
2 | Celtics | 30.347
3 | Kings | 12.322
4 | Kings | 25.918
5 | Mavs | 31.14
6 | Mavs | 16.34
7 | Celtics | 10.596
8 | Celtics | 14.007
9 | Kings | 20.654
10 | Celtics | 26.328
Suppose that we would like to round each of the values in the points column to the nearest 10.
We can use the following syntax to do so:
SELECT *,
ROUND(points, -1) AS round_points
FROM athletes;
athleteid | team | points | round_points
-----------+---------+--------+--------------
1 | Mavs | 22.557 | 20
2 | Celtics | 30.347 | 30
3 | Kings | 12.322 | 10
4 | Kings | 25.918 | 30
5 | Mavs | 31.14 | 30
6 | Mavs | 16.34 | 20
7 | Celtics | 10.596 | 10
8 | Celtics | 14.007 | 10
9 | Kings | 20.654 | 20
10 | Celtics | 26.328 | 30
Notice that the resulting table contains a column named round_points that contains each of the original values from the points column rounded to the nearest 10.
For example, we can see:
- The value 22.557 is rounded to 20.
- The value 30.347 is rounded to 30.
- The value 12.322 is rounded to 10.
And so on.
Note: We used -1 to round to the nearest 10. Similarly, you can use -2 to round to the nearest 100, -3 to round to the nearest thousand, etc.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: How to Select Rows Having Count Greater Than
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: How to Find Duplicate Records in a Table
