How to fix Aggregate function calls cannot be nested in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 275 views

PostgreSQL error 42803 occurs when you attempt to nest one aggregate function (like COUNT, SUM, AVG) directly inside another aggregate function. Use subqueries, CTEs, or window functions to break the nesting into multiple query levels.

Quick fix

Fixes 42803

-- This fails:
SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id;

-- Or:
SELECT AVG(SUM(amount)) FROM sales GROUP BY product_id;
  1. Identify the nested aggregate pattern
  2. Use a subquery to separate aggregation levels
  3. Alternatively, use a Common Table Expression (CTE)
  4. Consider window functions for analytical queries
postgresql — error
-- This fails:
SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id;
-- Or:
SELECT AVG(SUM(amount)) FROM sales GROUP BY product_id;
Aggregate function calls cannot be nested

What this error means

Error 42803 ("Aggregate function calls cannot be nested") occurs when PostgreSQL encounters an aggregate function call inside the parameter list of another aggregate function at the same query level. PostgreSQL's query execution model processes aggregates in a single pass per query level, making it impossible to nest them directly. For example, you cannot write MAX(COUNT(*)) or AVG(SUM(price)) in a single SELECT clause. This restriction exists because aggregate functions operate on sets of rows and produce a single result value. Nesting them would create ambiguity about which rows to aggregate first. PostgreSQL resolves this by prohibiting direct nesting and requiring you to explicitly separate the aggregation levels using subqueries, Common Table Expressions (CTEs), or window functions.

Typical symptoms

  • Query fails with "ERROR: aggregate function calls cannot be nested"
  • Attempting to use MAX(COUNT(*)) or similar nested aggregates fails
  • JSONB_AGG(SUM(...)) or other combinations produce the error
  • Complex queries with multiple levels of aggregation fail at execution time
  • Subqueries that themselves return aggregates cannot be wrapped in outer aggregates

Common causes

  1. Directly nesting aggregate functions like MAX(COUNT(*)) in the same SELECT clause
  2. Using aggregate functions as arguments to other aggregate functions (e.g., SUM(AVG(amount)))
  3. Attempting to apply an aggregate to the result of another aggregate function without intermediate steps
  4. Nested aggregates in HAVING clauses (e.g., HAVING SUM(COUNT(*)) > 5)
  5. Using aggregates within aggregate function arguments in window functions without proper structure

How to fix "Aggregate function calls cannot be nested"

1Identify the nested aggregate pattern

First, locate the problematic query. It will have one aggregate function nested inside another:

sql
-- This fails:
SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id;

-- Or:
SELECT AVG(SUM(amount)) FROM sales GROUP BY product_id;
2Use a subquery to separate aggregation levels

Wrap the inner aggregate in a subquery, then apply the outer aggregate to the subquery result:

sql
-- Fixed with subquery:
SELECT MAX(order_count) 
FROM (
  SELECT COUNT(*) as order_count 
  FROM orders 
  GROUP BY customer_id
) subquery;

The key is that the inner aggregate (COUNT) completes in the subquery, producing one row per customer. The outer MAX then aggregates those results.

3Alternatively, use a Common Table Expression (CTE)

CTEs can be clearer and more readable than nested subqueries:

sql
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) as total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(total_amount) as avg_customer_spending
FROM customer_totals;

This separates the two aggregation steps clearly: first compute per-customer totals, then compute the average across customers.

4Consider window functions for analytical queries

If you need both the aggregate value and row-level detail, use window functions instead:

sql
SELECT 
  customer_id,
  order_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) as customer_total,
  AVG(SUM(amount) OVER (PARTITION BY customer_id)) OVER () as avg_customer_total
FROM orders;

Window functions avoid aggregation altogether by computing results "alongside" the original rows, eliminating the need for nested aggregates.

5For JSONB_AGG and array aggregates, use subqueries

If nesting JSONB_AGG, ARRAY_AGG, or STRING_AGG within other aggregates, apply the same subquery pattern:

sql
-- This fails:
SELECT COUNT(DISTINCT jsonb_agg(tags))...

-- Use subquery:
SELECT COUNT(DISTINCT tag_group)
FROM (
  SELECT jsonb_agg(tags) as tag_group
  FROM products
  GROUP BY category
) sub;

Advanced notes

Why PostgreSQL Prohibits Nested Aggregates: PostgreSQL parses and executes aggregate functions in a single pass per query level. The FROM clause defines the row set, GROUP BY partitions it, and each aggregate in the SELECT list processes its partition independently. Nesting aggregates would require PostgreSQL to keep multiple aggregation states in memory simultaneously or perform multiple passes, complicating the executor. This design choice improves performance and query clarity. In some OLAP databases or analytical tools, nested aggregates are allowed because they use different execution strategies (e.g., hierarchical processing). PostgreSQL's approach is more conservative and focuses on clarity. If you find yourself writing deeply nested aggregates, consider whether window functions, CTEs, or denormalization might be better solutions. For very complex analytical queries, migrating to a specialized OLAP database may be necessary, but for typical SQL operations, the subquery/CTE pattern is clear and performant.

Frequently asked questions

What does "42803" mean?
Error 42803 ("Aggregate function calls cannot be nested") occurs when PostgreSQL encounters an aggregate function call inside the parameter list of another aggregate function at the same query level. PostgreSQL's query execution model processes aggregates in a single pass per query level, making it impossible to nest them directly. For example, you cannot write MAX(COUNT(*)) or AVG(SUM(price)) in a single SELECT clause.
How do I fix "42803"?
PostgreSQL error 42803 occurs when you attempt to nest one aggregate function (like COUNT, SUM, AVG) directly inside another aggregate function. Use subqueries, CTEs, or window functions to break the nesting into multiple query levels.
What causes "42803"?
Directly nesting aggregate functions like MAX(COUNT(*)) in the same SELECT clause Using aggregate functions as arguments to other aggregate functions (e.g., SUM(AVG(amount))) Attempting to apply an aggregate to the result of another aggregate function without intermediate steps
Is the "42803" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading