CODEWITHSUNDEEP
mysql
user3296668
user3296668

Reputation: 75

COUNT DUPLICATE RECORDS IN MYSQL

I need a query to count the duplicate records,

For Example

table name -customer
===================

customer_id-col name
222
111
222
222
111
122

output would be

customer_id   count

222              3
111              2
222              3
222              3
111              2
122              1

i tried this query

SELECT customer_id,count( customer_id ) c FROM customer GROUP BY customer_id HAVING c >1

output is

customer_id count

222              3
111              2
122              1

Is this possible Thanks in advance

Thanks Raja

Upvotes: 0

Views: 102

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT T.customer_id,S.duplicate_count FROM
    (
    SELECT customer_id,count(customer_id) AS duplicate_count
    FROM yourtable group by customer_id
    HAVING (duplicate_count > 0)
    ) AS S Join yourtable On S.customer_id = T.customer_id

FIDDLE DEMO

OP:

 customer_id       count

    222              3
    111              2
    222              3
    222              3
    111              2
    122              1

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

select customer_id,count(1) as count
from customer
group by customer_id

Upvotes: 0

Related Questions