Nested Aggregates and DISTINCT

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

Use DISTINCT inside functions like COUNT() or AVG() to eliminate duplicates.

Copy
SELECT COUNT(DISTINCT client_id)
FROM client_orders;

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

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

AVG(DISTINCT column) is not the same as AVG(column), the former excludes duplicate values.

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

The key differences are:

• In SELECT: DISTINCT is the SQL standard. UNIQUE is a non-standard synonym supported only in some systems.

• In CREATE TABLE: Only UNIQUE is valid, as it's a constraint rather than a query modifier.

When you need to aggregate over another aggregated result, use a subquery.

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 for each region, and the outer query finds the highest average among them.