I want a query to count the duplicate values in a column, e.g.,
total records=SELECT COUNT(column_name) FROM table_name;
distinct records=SELECT COUNT(DISTINCT column_name) FROM table_name;
duplicate count=total records-distinct records;
-
2Choose one database please don't tag all the available databasePரதீப்– Pரதீப்2015-01-19 05:25:02 +00:00Commented Jan 19, 2015 at 5:25
Add a comment
|
5 Answers
A simplification of the SQL you provided:
SELECT Count(Column) - Count(DISTINCT Column)
FROM yourTable
3 Comments
Rajasekhar
this works perfectly... and i have one more doubt,is distinct count and unique count were same or there is any difference.
Deep
check this question : stackoverflow.com/questions/6772451/…
ronline
Lot of examples on the web go into unnecessary junction. This one is the shortest an most efficient.
SELECT COUNT(column_name) as count
FROM table_name
GROUP BY column_name
HAVING count > 1
Comments
select column_name, count(*)
from table_name
group by column_name
having count(*) > 1
Comments
SELECT (SELECT COUNT(col) FROM tbl) - (SELECT COUNT(DISTINCT col) FROM tbl);
EDIT: Good point by NoDisplayName. This works in MySQL at least, I don't guarantee cross-engine compatibility (I last worked on Oracle fifteen years ago, and on SQL Server never)
Comments
A subquery can be used:
select sum(duplicates) from
(select column_name, num_duplicates-1 as duplicates from
(select column_name,count(column_name) as num_duplicates from table_name
group by column_name
having count(column_name)>1) as table_alias1) as table_alias2
