SQL Distinct keyword is used to select unique set of values for a column in a table.
SQL Distinct
- It is very often that we have duplicate data available as part of the data storage. For example, as part of an address table, the state column can have the same value multiple times.
- In such cases when we need to identify the unique set of values we use SQL DISTINCT keyword along with SELECT to identify the unique values.
- SQL DISTINCT looks through the list of values and identifies the unique values from the list.
SQL Select Distinct Syntax
SELECT DISTINCT column FROM table_name;
In the syntax above the distinct values are retrieved from the column that is mentioned as part of the query.
Let’s try to understand the DISTINCT command through some example.
SQL Distinct Example
Let us consider the following Customer Table to understand SQL DISTINCT query.
CustomerId | CustomerName | CustomerAge | CustomerGender |
---|---|---|---|
1 | John | 31 | M |
2 | Amit | 25 | M |
3 | Annie | 35 | F |
Scenario: Identify unique genders from Customer table.
Query:
SELECT DISTINCT CutomerGender FROM Customer;
CustomerGender |
---|
M |
F |
SQL Count Distinct Values
Sometimes we want to count the total number of distinct values. For example, find out the count of unique gender in the Customer table. Here we will have to use SQL Count function and distinct clauses together within SQL Select statement. We will use the inner select query to first find out the unique gender values and the use count function on it.
SELECT Count(*) As DistinctGender FROM (SELECT DISTINCT CutomerGender FROM Customer);
SQL Distinct Values with Count
What if we want to find the gender and their count in the customer table. We can achieve this using group by clause with distinct clause and count function.
SELECT DISTINCT CustomerGender, Count(*) as Occurs FROM Customer GROUP BY CustomerGender;
CustomerGender | Occurs |
---|---|
M | 2 |
F | 1 |
That’s all for SQL Distinct clause with examples. If you are looking for any specific scenario, please comment and I will try to respond with the solution.