CODEWITHSUNDEEP
sqlpostgresqlgroup-byhaving
user3839717
user3839717

Reputation:

how to get duplicate count and the corresponding value postgresql

I have a table called duptable and which contains following data.

mid mgp

1  cat    
1  cat1    
1  cat2    
1  cat3    
1  cat4    
2  rat    
3  bat    
3  bat1    
3  bat2    
3  bat3

as you can see in the above table mid 1 repeats 5 times and mid 3 repeats 4 times.How can I get result like below

  mid dup_count
    1  5
    3  4

Upvotes: 1

Views: 39

Answers (1)

Vivek S.
Vivek S.

Reputation: 21915

SELECT
 mid,
 count(mid) dup_count 
FROM duptable 
GROUP BY mid HAVING count(mid) >1

Upvotes: 1

Related Questions