Get the number of duplicate names and their frequency
Question
Table – Employee
Name
Nitin
Amit
Gaurav
Nitin
Amit
Output
Nitin – 2
Amit – 2
Gaurav – 1
in progress
1
SQL
4 years
11 Answers
1632 views
Grand Master 0
Answers ( 11 )
Sorry it a private answer.
1) This will give you the duplicate count
select Name, count(Name) from Employee
Group by Name
Having count(Name) > 1
2) This will give you the output given in the question
select Name, count(Name) from Employee
Group by Name
select Name as duplicate_name , count(1) as frequency
from Employee
GROUP BY Name
— if you want only the duplicate names
select name, count(name)
from employees
group by name having count(name) > 1
–returning all names and their frequency
select name, count(name) as frequency
from employees
group by name
SELECT Name, count(Name) as Frequency FROM Employee GROUP BY Name;
SELECT Name, count(Name) FROM table GROUP BY Name;
SELECT name ,COUNT(*) AS frequency
FROM names
GROUP BY name
According to the OUTPUT given:
Select Name, COUNT(Name) as frequency
from Employees
GROUP BY Name;
* Duplicate Names:
SELECT name
FROM Employees
GROUP BY name
HAVING count(name) > 1
* Names with their frequency:
SELECT name, Count(name) as frequency
FROM Employees
GROUP BY name
select first_name, count(first_name) from actor
group by first_name
having count(first_name) > 1;
SELECT name, COUNT(name) from Employee group by name
having COUNT(name) >1