Nested Aggregates and DISTINCT

SQL lets you combine aggregate functions with DISTINCT and use nested queries (subqueries) for advanced summaries and comparisons.

You can use DISTINCT inside aggregate functions like COUNT() or AVG() to remove duplicate values.

Copy
SELECT COUNT(DISTINCT client_id)
FROM client_orders;

COUNT(DISTINCT client_id) returns the number of unique clients who made orders.

Below is an example of using DISTINCT with AVG().

Copy
SELECT AVG(DISTINCT order_total)
FROM client_orders;

AVG(DISTINCT order_total) calculates the average based only on unique order amounts.

Note: AVG(DISTINCT column) is not the same as AVG(column), it excludes duplicate values.

DISTINCT and UNIQUE are almost the same - both remove duplicates from the result set.

Key differences:

• In a SELECT query: DISTINCT is the SQL standard; UNIQUE is a non-standard synonym used in some systems.

• In CREATE TABLE: Only UNIQUE is valid — it’s a constraint, not a query modifier.

Use a subquery when you need to apply an aggregate function to the result of another aggregation.

A subquery is simply a query inside another query.

Below is an example of using a subquery to find the region with the highest average order value:

Copy
SELECT MAX(avg_total)
FROM (
  SELECT region, AVG(order_total) AS avg_total
  FROM clients
  JOIN client_orders ON clients.id = client_orders.client_id
  GROUP BY region
) AS region_averages;

The inner query calculates the average order value per region, while the outer query selects the maximum average among those regions.