Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
count
distinct
database

How to use SQL COUNT with DISTINCT

Educative Answers Team

A combination of COUNT and DISTINCT is used in SQL if one wants to get a count of the number of rows in a table, counting unique entries only.

Syntax


SELECT COUNT(DISTINCT column) FROM table;

This statement would count all the unique entries of the attribute column in the table. DISTINCT ensures that repeated entries are only counted once.

Example

Consider the following table, employees, created as follows:

CREATE TABLE employees(
  emp_id int,
  emp_name varchar(20),
  dept varchar(20),
  age int
);


INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(1, "John", "Intern",25);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(2, "David", "Intern",30);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(3, "Mike", "Engineer",29);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(4, "Alex", "HR",27);
id name dept age
1 John Intern 25
2 David Intern 30
3 Mike Engineer 29
4 Alex HR 27
SELECT COUNT(DISTINCT dept) from employees

This SQL command counts all distinct department names from the employee’s table.

The SQL command returns 3 since there is 3 unique dept. There are two employees who are Intern, therefore the DISTINCT clause only counts them as one. The other two dept: Engineer and HR are unique, so they are each counted once as well - giving a total of 3.

RELATED TAGS

sql
count
distinct
database
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring