Rima Joy Rima Joy - 12 days ago 5
MySQL Question

count of a column in the result set on which distinct is already applied

Consider the table

Property
.

KeyIdNum|Property|IdNum
1 12 1234
1 12 1234
1 44 1234
1 12 1234
1 56 1234
2 12 4567
3 12 6789
3 56 6789
3 12 6789
4 44 3434
5 12 4444
6 44 9999
6 44 9999


It contains property num associated with each id num.But it contains duplicates.
I applied
distinct
to avoid duplicates.

select distinct KeyIdNum,Property,IdNum from Property.


So i got the result as :

KeyIdNum |Property |IdNum
1 12 1234
1 44 1234
1 56 1234
2 12 4567
3 12 6789
3 56 6789
4 44 3434
5 12 4444
6 44 9999


But now I want to `select( after applying distinct) ,the KeyIdNum (or IdNum) which are coming more than one time in the distinct result set shown above.
Please help me on this.I am not able to find a way to get the count of a column in the distinct result set using a single query.

Answer
select KeyIdNum,count(KeyIdNum) as count
From (
select distinct KeyIdNum,Property,IdNum from Table19 )A
 group by KeyIdNum

output

KeyIdNum    count
1           3
2           1
3           2
4           1
5           1
6           1