mysql sww the duplicate on colum1 colum2 colum3

teera
teera used Ask the Experts™
on
How can I cout the duplicate on colum1 and colum2 and colum3

I want the same address that duplicate

Hno  road  destrict     if 3 colums  is the same it mean it the same place
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technical Development Lead
Commented:
I think the following should show what you want.
SELECT Hno,  road, destrict 

FROM MyTable

GROUP BY Hno,  road, destrict 

HAVING COUNT(Hno) >1

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
List all duplicates

select t.*, x.cnt
from tbl t inner join (
select hno, road, district
from tbl
group by hno, road, district
having count(*) > 1
) AS x on x.hno=t.hno and x.road=t.road and x.district=t.district
order by t.hno, t.road, t.district
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
correction

select t.*, x.cnt
from tbl t inner join (
select hno, road, district, count(*) as cnt
from tbl
group by hno, road, district
having count(*) > 1
) AS x on x.hno=t.hno and x.road=t.road and x.district=t.district
order by t.hno, t.road, t.district

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial