How to Round an Average to 2 Decimal Places in PostgreSQL

By the end of this page, you will understand why ROUND(AVG(column), 2) sometimes fails in PostgreSQL, how PostgreSQL handles double precision vs numeric, and how to fix the problem by casting values to the correct type before rounding.

PostgreSQL has different numeric data types, and function behavior depends on the type of value you pass in.

In this case, the important idea is:

• AVG(...) does not always return the same type.
• ROUND(...) has different supported signatures depending on the type.

A common cause of this error is that AVG(some_column) is returning a double precision value, while ROUND(value, 2) in PostgreSQL expects a numeric value for the two-argument version.

Why the error happens

PostgreSQL supports:

ROUND(double precision)

but the two-argument version is for numeric:

ROUND(numeric, integer)

So this works:

SELECT ROUND(AVG(some_column))
FROM table_name;

because PostgreSQL can apply the one-argument ROUND to a floating-point result.

Think of PostgreSQL functions like tools with specific socket sizes.

• ROUND(value) is a tool that fits one kind of input.
• ROUND(value, 2) is a more specialized tool.
• If you hand PostgreSQL a double precision value when it expects numeric, the tool does not fit.

Casting is like putting an adapter on the value so it fits the correct function.

So instead of saying:

ROUND(AVG(some_column), 2)

you say:

ROUND(AVG(some_column)::numeric, 2)

Now PostgreSQL sees a numeric value and can use the correct version of ROUND.

The usual fix is to cast the average to numeric before rounding.

SELECT ROUND(AVG(some_column)::numeric, 2)
FROM table_name;

You can also use CAST(...):

SELECT ROUND(CAST(AVG(some_column) AS numeric), 2)
FROM table_name;

Example

Suppose you have values like this:

SELECT AVG(score) AS avg_score
FROM results;

If AVG(score) returns something like 87.6666666667, you can round it to two decimal places:

SELECT ROUND(AVG(score)::numeric, 2) AS avg_score
FROM results;

Consider this query:

SELECT ROUND(AVG(price)::numeric, 2) AS average_price
FROM products;

Step by step:

1. PostgreSQL reads values from the price column.
2. AVG(price) calculates the mean value.
3. The result is cast to numeric using ::numeric.
4. ROUND(..., 2) rounds that numeric result to 2 decimal places.
5. The final rounded value is returned as average_price.

Small trace example

Imagine the price values are:

10.10
10.20
10.25

The average is:

(10.10 + 10.20 + 10.25) / 3 = 10.183333...

After casting and rounding:

SELECT ROUND(10.183333::numeric, );

Rounding averages to fixed decimal places is common in many kinds of software.

Reporting dashboards

You may show average order value, average session duration, or average review score:

SELECT ROUND(AVG(order_total)::numeric, 2)
FROM orders;

Finance and billing

Money values usually need exact decimal handling:

SELECT ROUND(AVG(invoice_amount)::numeric, 2)
FROM invoices;

Education platforms

Average exam scores are often shown with one or two decimal places:

SELECT ROUND(AVG(mark)::numeric, 2)
FROM exam_results;

APIs

An API response may return rounded aggregates for frontend display:

SELECT ROUND(AVG(response_time_ms)::numeric, 2) AS avg_response_time
FROM request_logs;

In real projects, developers usually combine rounding with a few common patterns.

1. Aliasing the result

Give the rounded value a clear column name:

SELECT ROUND(AVG(score)::numeric, 2) AS average_score
FROM results;

2. Handling NULL values

If there are no rows, AVG(...) returns NULL:

SELECT COALESCE(ROUND(AVG(score)::numeric, 2), 0) AS average_score
FROM results;

3. Filtering before averaging

Often averages only apply to valid rows:

SELECT ROUND(AVG(score)::numeric, 2) AS average_score
FROM results
WHERE score IS NOT NULL;

4. Grouped reports

Mistake 1: Using ROUND(value, 2) on double precision

Broken example:

SELECT ROUND(AVG(some_column), 2)
FROM table_name;

Why it fails:

• AVG(some_column) may be double precision
• PostgreSQL does not support round(double precision, integer)

Fix:

SELECT ROUND(AVG(some_column)::numeric, 2)
FROM table_name;

Mistake 2: Confusing storage type with display format

Rounding affects the numeric result, but some client tools may still display values in their own format.

Example:

SELECT ROUND(AVG(score)::numeric, 2)
FROM results;

This rounds the value, but how it appears in an app may depend on Ruby, Sequel, or the frontend.

Mistake 3: Rounding too early

Fix for the error

SELECT ROUND(AVG(some_column)::numeric, 2)
FROM table_name;

Equivalent cast syntax

SELECT ROUND(CAST(AVG(some_column) AS numeric), 2)
FROM table_name;

Key rule

• ROUND(double precision) exists
• ROUND(numeric, integer) exists
• ROUND(double precision, integer) does not exist

Common patterns

-- Basic average rounded to 2 decimals
SELECT ROUND(AVG(score)::numeric, 2) FROM results;

-- With alias
SELECT ROUND(AVG(score)::numeric, 2) AS average_score FROM results;


 (ROUND((score)::, ), )  results;


 category_id, ROUND((price)::, )
 products
  category_id;

Why does ROUND(AVG(column), 2) fail in PostgreSQL?

Because the result of AVG(column) may be double precision, and PostgreSQL does not provide a round(double precision, integer) function.

How do I round to 2 decimal places in PostgreSQL?

Cast the value to numeric first:

SELECT ROUND(AVG(column)::numeric, 2)
FROM table_name;

Why does ROUND(AVG(column)) work without a cast?

Because PostgreSQL supports the one-argument form for floating-point values.

Should I use numeric or double precision for money values?

Use numeric for money and exact decimal calculations.

Does this problem come from Ruby Sequel?

No. The error comes from PostgreSQL function/type rules. Sequel is just showing the database error.

Can I use CAST(... AS numeric) instead of ?

• CAST in PostgreSQL - Related because casting changes a value to the type a function expects.
• PostgreSQL numeric data types - Related because numeric and double precision behave differently.
• AVG aggregate function - Related because the return type of AVG affects whether rounding works.
• ROUND function in SQL - Related because different overloads of ROUND support different argument types.
• COALESCE in PostgreSQL - Related because aggregate queries can return NULL when no rows match.
• GROUP BY in PostgreSQL - Related because averages are often calculated per group.
• Formatting numbers in SQL - Related because rounding and display formatting are similar but not identical concepts.