Skip to main content
Practice

Nested Aggregates and DISTINCT

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


DISTINCT with Aggregates​

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

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

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


What's the difference between DISTINCT and UNIQUE?​

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.


Nested Aggregates (Subqueries)​

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:

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

Want to learn more?

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