aggregate function calls cannot be nested

You put one aggregate inside another, like AVG(COUNT(*)). A single query has one level of grouping, and each aggregate collapses a group into one value. There’s no second level for the outer aggregate to work on.

Why it happens

The question behind the query is reasonable: how many products does a category have on average? Answering it takes two steps. First count products per category, then average those counts.

SELECT AVG(COUNT(*)) AS avg_products_per_category
FROM demo.products
GROUP BY category;

GROUP BY category sets up one group per category. COUNT(*) runs inside each group. AVG would need to run across the groups, over a set of rows that doesn’t exist yet in this query. The same goes for MAX(SUM(quantity)), SUM(AVG(price)) and every other nested pair.

How to fix it

Do the steps one after the other. Put the inner aggregate in a subquery, then aggregate its output:

SELECT AVG(product_count) AS avg_products_per_category
FROM (
  SELECT category, COUNT(*) AS product_count
  FROM demo.products
  GROUP BY category
) per_category;

The subquery returns three rows, 5, 5 and 2, and the outer query averages them to 4.

A CTE says the same thing with the steps in reading order, which helps once there are more than two:

WITH per_category AS (
  SELECT category, COUNT(*) AS product_count
  FROM demo.products
  GROUP BY category
)
SELECT AVG(product_count) AS avg_products_per_category
FROM per_category;

The pattern works for any pair. The most units sold of any single product:

SELECT MAX(units) AS best_seller_units
FROM (
  SELECT product_id, SUM(quantity) AS units
  FROM demo.order_items
  GROUP BY product_id
) per_product;

Keeping the groups alongside the result

Sometimes you want every category with its own count and the overall average next to it. A window function can wrap an aggregate, because windows are computed after grouping:

SELECT
  category,
  COUNT(*)                AS product_count,
  AVG(COUNT(*)) OVER ()   AS avg_per_category
FROM demo.products
GROUP BY category;

AVG(...) OVER () isn’t a nested aggregate. It averages COUNT(*) across all the grouped rows. Each category comes back with its count and the average of 4.

Oracle is the exception

Oracle accepts one level of nesting when the query has a GROUP BY, so SELECT AVG(COUNT(*)) FROM products GROUP BY category returns a single row there. Queries written that way need the subquery form when you move them to any other database.