CODEWITHSUNDEEP
mysqlsum
86Stang
86Stang

Reputation: 349

Totaling the number of duplicates

I've got this to find all duplicates in my db:

SELECT phone, count(*) AS ct FROM table GROUP BY `phone` HAVING ct >1

This results in 12 rows but the sum of ct of those 12 rows equals 26: 

phone      ct
3600000000  4
3602931205  2
3602933399  2
3602933736  2
3602936241  2
3602937911  2
3602939287  2
3602939353  2
3602939767  2
3603174530  2
3606612300  2
3608995913  2

How can I modify the query to tell me the total of ct?

Upvotes: 1

Views: 46

Answers (1)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

Select SUM(temp_1.count_1) as totalCount
from
(
SELECT phone, count(1) AS count_1 FROM table GROUP BY `phone` HAVING count_1 >1) as temp_1

Upvotes: 2

Related Questions