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.

But this fails:

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

when AVG(some_column) is double precision, because PostgreSQL cannot find a function matching:

round(double precision, integer)

Why this matters

In real applications, you often need to:

• format averages for reports
• round prices, percentages, and scores
• control decimal precision in analytics queries

To do that correctly in PostgreSQL, you need to know when to use:

• double precision for floating-point math
• numeric for exact decimal rounding

For fixed decimal places, numeric is usually the right type.

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;

Result:

87.67

Formatting vs rounding

Rounding changes the numeric value to a chosen precision.

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

If you need display formatting, that is a separate concern. For example, reports may also use formatting functions, but for most SQL calculations, ROUND(..., 2) with numeric is the key fix.

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, 2);

Result:

10.18

What fails

This version may fail:

SELECT ROUND(AVG(price), 2)
FROM products;

because PostgreSQL may interpret AVG(price) as double precision, and there is no matching round(double precision, integer) function.

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;

Data analysis

When generating summary tables, analysts often round output for readability while keeping raw data unrounded elsewhere.

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

You may round averages per category:

SELECT category_id, ROUND(AVG(price)::numeric, 2) AS avg_price
FROM products
GROUP BY category_id;

5. Keeping raw vs display values separate

In mature codebases, teams often keep:

• the raw aggregate for internal calculations
• the rounded aggregate for display or export

Example:

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

This avoids losing precision too early.

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

Broken idea:

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

This rounds each row first, then averages them. That can produce a different result.

Usually better:

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

Mistake 4: Forgetting NULL behavior

If there are no matching rows, the average is NULL.

SELECT ROUND(AVG(score)::numeric, 2)
FROM results
WHERE course_id = 9999;

This may return NULL, not 0.

Fix if needed:

SELECT COALESCE(ROUND(AVG(score)::numeric, 2), 0)
FROM results
WHERE course_id = 9999;

Mistake 5: Using floating-point types for exact decimal needs

For currencies and exact decimal values, numeric is often safer than floating-point types because floating-point values can have tiny precision artifacts.

ROUND(AVG(x)) vs ROUND(AVG(x)::numeric, 2)

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;

-- With NULL handling
SELECT COALESCE(ROUND(AVG(score)::numeric, 2), 0) FROM results;

-- Grouped average
SELECT category_id, ROUND(AVG(price)::numeric, 2)
FROM products
GROUP BY category_id;

When to use numeric

Use numeric when you need:

• exact decimal precision
• fixed decimal places
• money/reporting-style output

Edge case

If there are no rows to average, AVG(...) returns NULL. Use COALESCE(...) if you want a default value.

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 ::numeric?

Yes. Both are valid:

ROUND(CAST(AVG(column) AS numeric), 2)

Should I round before or after averaging?

Usually after averaging:

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

Rounding each row first can change the final result.

• 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.