CODEWITHSUNDEEP
sql
letsforum
letsforum

Reputation: 27

Find duplicate rows in 2 fields?

Can you teach me how to find duplicate or same values in different rows?

SELECT * FROM `geoname`

Now I need to compare these rows for duplicates:

WHERE `feature code` = 'ADM1'

WHERE `feature code` = 'ADM2'

UPDATE.

Column NAME contains names that can be duplicates. I wanna find ones?

Upvotes: 0

Views: 39

Answers (2)

Jani Devang
Jani Devang

Reputation: 1109

SELECT geo.*,count(*) as Duplicate_Count
   FROM dbo.geoname AS geo
   having count(*) > 1
   group by geo.*
WHERE `feature code` = 'ADM1' OR `feature code` = 'ADM2'

This Query will return record with its duplicate count,how many times duplication occurred.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

To get a count of the occurrences of these two feature codes you can try conditional aggregation:

SELECT SUM(CASE WHEN `feature code` = 'ADM1' THEN 1 ELSE 0 END) AS adm1count,
       SUM(CASE WHEN `feature code` = 'ADM2' THEN 1 ELSE 0 END) AS adm2count
FROM yourTable

Update: If you want to find out which names occur in duplicate then you can try:

SELECT NAME
FROM yourTable
GROUP BY NAME
HAVING COUNT(*) > 1

Upvotes: 2

Related Questions