How to use SQL COUNT with DISTINCT
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.
Copyright ©2024 Educative, Inc. All rights reserved
TRENDING TOPICS
