Overview
This lecture explains how to count the number of distinct values in SQL (specifically, unique passengers who flew business class), using nested queries to overcome SQL's limitations with COUNT and DISTINCT.
Counting Distinct Values in SQL
- COUNT alone does not work directly with DISTINCT in Microsoft Access and SQL.
- Attempting COUNT(DISTINCT passenger.name) produces an error in some SQL dialects.
- To count distinct values, first select distinct passenger names in a subquery.
Using Nested (Sub)Queries
- SQL treats subquery results as a table, allowing further operations like COUNT.
- Wrap the SELECT DISTINCT query in parentheses as a subquery after FROM.
- Use SELECT COUNT() AS [count of unique passengers] FROM (subquery) to get the number of unique records.
Flexibility with Subqueries
- The subquery can include multiple columns (e.g., passenger.name, passenger.address) and still produce the correct unique count.
- The COUNT in the outer query will always reflect the number of unique rows returned by the subquery.
Key Terms & Definitions
- DISTINCT — SQL keyword that returns only unique values in a result set.
- COUNT — SQL function that returns the number of rows in a result set.
- Subquery/Nested Query — A query embedded within another query, often used as a virtual table.
Action Items / Next Steps
- Practice writing SQL queries that use SELECT DISTINCT inside a subquery to count unique values.
- Review the use of the WHERE keyword in filtering query results before the next lecture.