TryBench
Data Analysis & SQL · Foundation lesson 7 of 19

Unique Values & Averages

Use DISTINCT, AVG, MIN, and MAX to describe a dataset in a single summary row.

Why this matters

A quick summary of the data is the first thing analysts check. These four functions give you that in a single row.

DISTINCT Removes Duplicates

SELECT DISTINCT region lists each region once. COUNT(DISTINCT region) counts how many different regions there are.

In real life: A class of 40 pupils may come from only 6 different schools. COUNT(*) says 40. COUNT(DISTINCT school) says 6.

SELECT COUNT(DISTINCT region) AS regions_served
FROM orders;

AVG, MIN, and MAX

AVG is the average. MIN is the smallest. MAX is the largest. All three skip blank (NULL) values.

In real life: In a class test, the average is the typical mark, MIN is the lowest mark, and MAX is the highest. Together they describe the whole class in one line.

ROUND Tidies Decimals

ROUND(AVG(amount), 2) keeps two numbers after the decimal point, so reports look neat.

In real life: Instead of showing 20,041.25000001 in a report, ROUND shows 20,041.25. The number is the same, but it is clean.

Your practice task

Finance wants a one-line health check of completed sales. Return the number of different regions with completed orders as regions_served, the average order amount rounded to 2 decimals as avg_order_value, the smallest amount as smallest_order, and the largest as largest_order.

How to do it

  1. Find the four numbers the brief asks for.
  2. Pick the right summary function for each one.
  3. Remember to count different regions, not rows, and to round the average.
  4. Keep only completed orders, then press Run proof.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Summary Functions

Each function collapses many rows into one value. Combine them in a single SELECT.

SELECT COUNT(DISTINCT col), AVG(col), MIN(col), MAX(col)
FROM table;

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL