SQL Select Distinct Count

Definition of SQL Select Distinct Count

It is used to retrieve a number of counts from the specified column which was unique in values. We are using one column from which we want to retrieve the unique count of values. It is very useful and important in any RDBMS to retrieve the unique count of records. It eliminates the same data which came repeatedly.

Overview of SQL Select Distinct Count

• Basically select distinct count is retrieving the count of all unique records from the table. It will eliminate all the duplicate records from the table. Suppose our table contains more duplicate records, and we want a count of only unique records same time we are using SQL select a distinct count in our query.
• We can use it on a specified column. This function will remove duplicate records from the table and display the unique count of records. It will work on single columns as well as multiple columns.
• It will work on multiple columns, we can use on the single column from the table from which we have retrieved the unique count of records.
• We can use statements with aggregation like min, max, avg, count, etc.

How to use SQL SELECT DISTINCT?

• Below syntax shows how to use sql select distinct statements. In the below syntax, we are using count with sql select distinct.

Syntax –

• SELECT COUNT(DISTINCT name_of_column) FROM name_of_table;
• SELECT COUNT(DISTINCT name_of_column) FROM name_of_table where condition;
• Select DISTINCT name_of_column1, name_of_column2, …., name_of_columnN

From name_of_table where condition;

• Select COUNT(DISTINCT name_of_column1), COUNT(DISTINCT name_of_column2), …, COUNT(DISTINCT name_of_columnN) from name_of_table;
• Below is the description syntax of SQL select distinct with count statement is as follows.
• Count – We are using count with SQL select distinct for retrieving a number of unique records from specified columns. It will retrieve the number of unique records.
• Select – We can select the data as per the condition which was given in the query. This is the SQL statement that was used to select the specified data from a table. We can use select with a distinct count statements to retrieve unique count from the column.
• Name of column – This is the table column that was used with count and distinct keywords to retrieve a number of unique records from a table. Suppose we have used a distinct statement with a specified column it will retrieve the distinct records from the table.
• Name of table – We need to define the table name while using sql select a distinct count statement. This is the name of table from which we have retrieved the unique records.
• Where condition – Where the condition is very useful to retrieve specific condition, unique row count, by using this statement. The where condition in any statement of SQL will use to select or retrieved a specified row that we have defined in the condition.
• At the time of using one expression by using a clause, our query will return the unique count of records from the expressions.
• We can ignore the null values by using the statement while retrieving a number of records by using the statement, our result will not give the count of null values.
• In the below example, we can see that sql select statement count will ignore the null values from the specified column on which we are using sql select distinct count clause.

Select count (distinct id) from sql_distinct;

• In the above example, we can see that this statement will ignore the count of null values. It will only retrieve the unique count of not null values.
• We can also retrieve the unique count of a number of columns by using the statement. In the below example, we are retrieving the unique count of the id and name column. For retrieving the count of multiple columns we need to use count and distinct keywords multiple times.

Select count (distinct id), count (distinct name) from sql_distinct;

• In the above example, we can see that the id column will contain the 4 unique records and the name column will also contain the 4 unique records.

SQL SELECT DISTINCT Statement Example

• Below is the example of SQL select distinct with the count is as follows. We are using the sql_distinct_count table from a distinct database. We are using the postgres database to see the example of sql select distinct with the count. Below is the sample data from sql_distinct table.

Select * from sql_distinct_count;

1. In the below example, we have found the distinct count of records from the name column. We can see that the unique records count of name column is 4.

SELECT COUNT(DISTINCT name) FROM sql_distinct_count;

2. In the below example we have found distinct number of records from the id and name column. We are using count and distinct two times in a single query.

Select count (distinct id) as ID, count (distinct name) as Name from sql_distinct_count;

3. Below example shows the statement with where condition. We are using the where condition on the id column by using this statement. It will return a unique count of specified column values.

Select count(distinct id) from sql_distinct_count where id = 103;

4. Below example shows the statement with where condition. We are using the where condition on the id and name column by using this statement. It will return a unique count of specified column values.

Select count(distinct id), count(distinct name)  from sql_distinct_count where id = 101 and name = ‘ABC’;

• In the below example, we are using sql select distinct statements without using count keywords.

Select distinct id, name from sql_distinct_count;

Conclusion

We can use SQL select distinct counts on a specified column. The SQL select distinct count function will remove duplicate records from the table and display the unique count of records. SQL select distinct count is used to retrieve a number of count from specified column which was unique in values.

Recommended Article

This is a guide to SQL Select Distinct Count. Here we discuss the definition, overview, How to use SQL SELECT DISTINCT, examples along with code implementation and output. You may also have a look at the following articles to learn more –

1. PL/SQL varray
2. PL/SQL LIKE
3. PL/SQL UNION
4. PL/SQL Raise Exception