Subquery in FROM.

A subquery in FROM acts like a temporary table for this query and must have an alias: FROM (SELECT ...) AS t. The outer query then works on already summarized rows. That gives you a second layer of calculation, such as the average of sums — AVG(SUM(...)) is not allowed directly, but AVG over a subquery containing SUM is.

Use clear aliases for the subquery (t, customer_totals) and for computed columns (spent, orders_count), because the outer query refers to them by name.

Common mistakes: a missing alias (PostgreSQL raises an error); nesting an aggregate inside an aggregate without a subquery; averaging over all orders instead of over per-customer sums; forgetting to exclude the NULL customer when you do not want it as a group.

Small example:

SELECT t.customer_id, t.spent FROM (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) AS t WHERE t.spent > 100;