SQL Distinct, SQL Count Distinct, SQL Select Distinct

Filed Under: SQL
SQL Distinct

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages