Skip to main content
Practice

Nested Aggregates and DISTINCT

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


DISTINCT with Aggregates​

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

Count unique clients per region
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().

Average of distinct order values
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.


What's the difference between DISTINCT and UNIQUE?​

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.


Nested Aggregates (Subqueries)​

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:

Find the region with the highest average order value
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.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.