How to fix Group function is nested too deeply in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUM

PostgreSQL does not support nesting aggregate functions directly (such as AVG(MAX(column))). Use subqueries or Common Table Expressions (CTEs) to perform multi-level aggregations.

What this error means

This error occurs when you attempt to nest one aggregate function inside another aggregate function in a single query expression. Aggregate functions like SUM(), AVG(), MAX(), MIN(), and COUNT() compute a single consolidated result from multiple rows. PostgreSQL does not allow you to apply an aggregate function to the result of another aggregate function directly because it creates ambiguity in how the aggregation should be computed. While this error message is more commonly associated with Oracle databases, similar restrictions apply in PostgreSQL where they manifest as "aggregate function calls cannot be nested" (error 42803).

How to fix "Group function is nested too deeply"

1Identify the nested aggregate functions in your query

Look for patterns where an aggregate function is used as the argument to another aggregate function. For example:

sql

-- This fails:
SELECT AVG(SUM(amount)) FROM orders GROUP BY customer_id;

-- This also fails:
SELECT MAX(COUNT(*)) FROM users GROUP BY department_id;

Read the error message to identify which functions are conflicting.

2Rewrite using a subquery

Create an inner query that computes the first aggregation, then wrap it in an outer query that performs the second aggregation:

sql

-- Instead of:
SELECT AVG(SUM(amount)) FROM orders;

-- Use a subquery:
SELECT AVG(total_amount) FROM (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
) AS customer_totals;

The inner query (customer_totals) computes the sum per customer, and the outer query averages those sums.

3Alternatively, use a Common Table Expression (CTE)

CTEs provide a cleaner, more readable way to handle multi-level aggregations. They allow you to break the query into logical steps:

sql

-- Using CTE:
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(total_amount) AS average_customer_spend
FROM customer_totals;

This approach is more maintainable and easier to debug than deeply nested subqueries.

4For multiple aggregation levels, chain CTEs

If you need more than two levels of aggregation, create multiple CTEs in sequence:

sql

WITH level_one AS (
  SELECT category, SUM(price) AS category_total
  FROM products
  GROUP BY category
),
level_two AS (
  SELECT AVG(category_total) AS avg_category_total
  FROM level_one
)
SELECT MAX(avg_category_total) AS max_avg
FROM level_two;

Each CTE builds on the previous one, making the logic clear and avoiding function nesting.

5Verify the query returns expected results

Test your rewritten query to ensure it produces the correct output:

sql

-- Before (would fail):
SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

-- After (works):
WITH dept_avg_salary AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT MAX(avg_salary) AS highest_avg_salary
FROM dept_avg_salary;

-- Verify by running both parts separately:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
SELECT MAX(avg_salary) FROM (SELECT AVG(salary) as avg_salary FROM employees GROUP BY department_id);

How to fix Group function is nested too deeply in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "Group function is nested too deeply"

Advanced notes

Related errors

Official resources & further reading