Sql · Aggregate and GROUP BY
Distinct
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to DISTINCT
The
DISTINCT
keyword in SQL is used to return only unique values in the result set. It removes all duplicate rows from your query results.
SELECT DISTINCT column1, column2, ...
FROM table_name;
Let's see how DISTINCT works with our sales data:
SELECT DISTINCT product_name
FROM sales
ORDER BY product_name;
You can also use DISTINCT with multiple columns. In this case, SQL looks for unique combinations of those columns:
SELECT DISTINCT product_name, region
FROM sales
ORDER BY product_name, region;
DISTINCT vs GROUP BY
DISTINCT is simpler than GROUP BY and is used only to remove duplicates. GROUP BY is more powerful because it can be combined with aggregate functions to calculate summaries for each group.
Exercise 2 — Understanding DISTINCT
What would the following query return?
SELECT DISTINCT category FROM sales;
Options
• A list of all unique categories in the sales table
• A count of each category in the sales table
• All the categories with their product names
• The most common category in the sales table
Hint : Think about what the DISTINCT keyword does - it removes duplicates from the results.
Exercise 3 — Complete the DISTINCT Query
Complete the SQL query to show all unique regions where products in the 'Medicine' category are sold.
SELECT ____ region
FROM sales
WHERE category = ________
Options: DISTINCT UNIQUE 'Medicine' 'Battle' ; ,
Hint : Think about which keyword removes duplicates, and remember to specify the correct category in the WHERE clause.
Exercise 4 — GROUP BY vs DISTINCT
Both
GROUP BY
and
DISTINCT
can be used to remove duplicates, but they serve different purposes:
-
DISTINCT
simply removes duplicate rows from the result
-
GROUP BY
groups rows and allows you to perform aggregate functions on each group
Let's see the difference with an example:
SELECT DISTINCT category, region
FROM sales
ORDER BY category, region;
Now let's try with GROUP BY:
SELECT category, region
FROM sales
GROUP BY category, region
ORDER BY category, region;
Notice that both queries return the same results in this case. But if we want to also get some aggregate information, we need GROUP BY:
SELECT category, region, COUNT(*) as sales_count
FROM sales
GROUP BY category, region
ORDER BY category, region;
Exercise 5 — Match Queries with Results
Match each SQL query with its expected result:
Items to match
• SELECT DISTINCT product_name FROM sales;
• SELECT product_name, COUNT(*) FROM sales GROUP BY product_name;
• SELECT COUNT(DISTINCT region) FROM sales;
• SELECT DISTINCT category, region FROM sales;
Definitions
• List of all unique products
• Each product with its number of sales records
• Total number of different regions
• All unique category-region combinations
Exercise 6 — DISTINCT with COUNT
What is the difference between these two queries?
SELECT COUNT(*) FROM sales;
SELECT COUNT(DISTINCT product_name) FROM sales;
Options
• The first counts all rows, the second counts unique products
• The first counts products, the second counts categories
• They produce the same result
• The first is invalid syntax, the second is correct
Hint : Pay attention to how COUNT() works with and without DISTINCT - they count different things.