mysql - Check Status of the Duplicate Records -

lets have table named record 4 fields

id    (int 11 auto_inc)  email (var 50)  timestamp (int 11)  status (int 1) 

and table contains following data

now can see email address test@xample.com duplicated 4 times (the record lowest timestamp original 1 , copies after duplicates). can count number of unique records using

select count(distinct email) record 

i can find out email address duplicated how many times using

select email, count(id) record group email having count(id)>1 

but business question is

how many times status 1 on duplicate records?

for example:

• for test@example.com there no duplicate record having status 1
• for second@example.com there 1 duplicate record having status 1
• for third@example.com there 1 duplicate record having status 1
• for four@example.com there no duplicate record having status 1
• for five@example.com there 2 duplicate record having status 1

so sum of numbers 0 + 1 + 1 + 0 + 2 = 4

which means there 4 duplicate records had status = 1 in table

question

how many duplicate records have status = 1 ?

this new solution works better. removes first entry each email , counts rest. it's not easy read, if possible write in stored procedure works.

select sum(status)   dude d1   join (select email,                 min(ts) ts            dude           group email) mins   using (email)  d1.ts != mins.ts; 

sqlfiddle

original answer below

your own query find "which email address duplicated how many times using"

select email,         count(id) duplicates    record   group email  having count(id)>1 

can modified answer "how many duplicate records have status = 1"

select email,         count(id) duplicates_status_sum    record   group email   status = 1  having count(id)>1 

both these queries answer including original line it's "duplicates including original one". can subtract 1 sums if original 1 have status 1.

select email,         count(id) -1 true_duplicates    record   group email  having count(id)>1  select email,         count(id) -1 true_duplicates_status_sum    record   group email   status = 1  having count(id)>1