How to fix Group function is nested too deeply in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 154 views

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.

Quick fix

Fixes Group function is nested too deeply

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

-- This also fails:
SELECT MAX(COUNT(*)) FROM users GROUP BY department_id;
  1. Identify the nested aggregate functions in your query
  2. Rewrite using a subquery
  3. Alternatively, use a Common Table Expression (CTE)
  4. For multiple aggregation levels, chain CTEs
postgresql — error
-- This fails:
SELECT AVG(SUM(amount)) FROM orders GROUP BY customer_id;
-- This also fails:
SELECT MAX(COUNT(*)) FROM users GROUP BY department_id;
Group function is nested too deeply

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

Typical symptoms

  • Query fails with "aggregate function calls cannot be nested" or similar message
  • Attempts to use functions like AVG(SUM(column)) or MAX(COUNT(*)) fail
  • Multi-level aggregations in a single SELECT statement produce errors
  • Queries that worked in other databases fail when migrated to PostgreSQL

Common causes

  1. Directly nesting one aggregate function as the argument to another aggregate function
  2. Attempting to aggregate the result of a GROUP BY aggregation without intermediate storage
  3. Using nested aggregates like SUM(MAX(salary)) or AVG(COUNT(*)) in the same query level
  4. Misunderstanding that aggregate functions must be evaluated sequentially, not nested

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

Advanced notes

PostgreSQL's restriction on nested aggregates is by design. Aggregate functions operate at the query level and produce single scalar results. Attempting to nest them violates the fundamental semantics of aggregation. Some database systems (like Oracle) allow limited nesting but with strict depth limits. If you find yourself needing multiple aggregation levels frequently, consider whether your data model or query approach could be simplified. Additionally, window functions (OVER clause) can sometimes provide an alternative to nested aggregation for certain use cases—for example, running totals or ranking within groups. For very complex analytical queries, consider moving aggregation logic to the application layer where you have more flexibility.

Frequently asked questions

What does "Group function is nested too deeply" mean?
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.
How do I fix "Group function is nested too deeply"?
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 causes "Group function is nested too deeply"?
Directly nesting one aggregate function as the argument to another aggregate function Attempting to aggregate the result of a GROUP BY aggregation without intermediate storage Using nested aggregates like SUM(MAX(salary)) or AVG(COUNT(*)) in the same query level
Is the "Group function is nested too deeply" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading