SQL Distinct Tutorial with Examples

The SQL command is used in conjunction with the statement to return only distinct (different) values. Here's an example DISTINCT SQL query used to find only unique names of pharmaceutical manufacturers:

Here's a comparison of the above query's output which uses the keyword, versus a query without the keyword:

As you can see, the keyword finds and returns only unique values in the "manufacturer" column, and removed all duplicate manufacturer names.

DISTINCT For Data Exploration

DISTINCT can be particularly helpful when exploring a new data set. In many real-world scenarios, you will generally end up writing several exploratory queries in order to figure out what data you have access too, and how you might want to group or filter the data.

DISTINCT With Two Columns

If you include two (or more) columns in a SELECT DISTINCT clause, your results will contain all of the unique pairs of those two columns.

For example, imagine you worked at stock trading app Robinhood and had access to their trades dataset. Here's a SQL query that uses DISTINCT on two columns – user_id's and trade statuses:

Note: You only need to include DISTINCT once in your SELECT clause—you do not need to add it for each column name.

You can try this out yourself by running the with two columns query against data from a real Robinhood Data Scientist SQL Interview Question:

COUNT DISTINCT Example

You can use with aggregate functions – the most common one being . Here's an example that finds the number of unique user's who made trades:

Here's that query in action:

Notice that goes inside the COUNT() aggregate function, rather at the beginning of the SELECT statement.

While you could use DISTINCT with SUM or AVG, in practice it's rare to want to just sum or average just the unique values. When it comes to MAX and MIN, they aren't affected by DISTINCT – whether there are duplicates or not, the lowest/highest value in the dataset will be the same.

SQL COUNT DISTINCT Practice Exercise

Imagine you're given a table containing data on Amazon customers and their spending on products in different category. Write a query using to identify the number of unique products within each product category.

Example Sample Input:

Example Sample Output:

Next Tutorial: ARITHMETIC

So far, we've only been doing simple math, like , , covered in the aggregate functions tutorial.

Earlier, in the filtering data tutorial, we also covered simple comparison operators like (equals) and (less than or equal to).

In the next tutorial, we'll cover arithmetic, like , , etc.