How to use SQL COUNT with DISTINCT

widget

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.

New on Educative
Learn to Code
Learn any Language as a beginner
Develop a human edge in an AI powered world and learn to code with AI from our beginner friendly catalog
🏆 Leaderboard
Daily Coding Challenge
Solve a new coding challenge every day and climb the leaderboard

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved