Select Distinct in SQL
Table of Content:
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
- SELECT DISTINCT returns only distinct (different) values.
- SELECT DISTINCT eliminates duplicate records from the results.
- DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.
- DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.
The general syntax is:
Syntax:
SELECT DISTINCT column1, column2, ...columnN FROM table_name;
Can be used with COUNT and other aggregates
Syntax:
SELECT COUNT (DISTINCT columnName) FROM tableName
Example:
If you don't have table please create below table and practice it.
-- My database name is SQLExamples USE SQLExamples -- Create this below Employee table DROP TABLE Employee CREATE TABLE Employee( EmpId INT, EmpName VARCHAR(25), EmpAddress VARCHAR(100), EmpDept VARCHAR(25) ) -- Insert data inside table INSERT INTO Employee VALUES (1, 'Rambo', 'Kolkata', 'IT'), (2, 'Rohit', 'Kolkata', 'IT'), (3, 'Rohon', 'Kolkata', 'ITIS'), (4, 'Ronok', 'Kolkata', 'ITIS'), (5, 'Rubin', 'Kolkata', 'ITIS'), (6, 'Sorif', 'Kolkata', 'ADMIN'), (7, 'Soriful', 'Kolkata', 'ADMIN'), (8, 'Sofik', 'Kolkata', 'ADMIN')
SELECT * FROM Employee
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
1 |
Rambo |
Kolkata |
IT |
2 |
Rohit |
Kolkata |
IT |
3 |
Rohon |
Kolkata |
ITIS |
4 |
Ronok |
Kolkata |
ITIS |
5 |
Rubin |
Kolkata |
ITIS |
6 |
Sorif |
Kolkata |
ADMIN |
7 |
Soriful |
Kolkata |
ADMIN |
8 |
Sofik |
Kolkata |
ADMIN |
Code: How many Unique EmpDept are present
SELECT DISTINCT EmpDept FROM Employee
Output:
The above code will produce the following result-
EmpDept ADMIN IT ITIS
Code: How many unique dept are present
SELECT COUNT (DISTINCT EmpDept) as DeptCount FROM Employee
Output:
The above code will produce the following result-
DeptCount 3