CODEWITHSUNDEEP
mysqlsqlgroup-byduplicateshaving
MrShabana
MrShabana

Reputation: 377

Count duplicate rows only based on 2 columns

assume i have the following table named test with 2 columns A and B
A B
------
1 2
2 3
1 2
3 4
I would like to use a query that would return only the count of duplicate rows (1 and 3 in this case) based on columns A and B combined, so the count returned should be 2.
SELECT 'Duplicates' As Warning, count(*) AS RowCount FROM test where(query goes here) , thanks.

Upvotes: 1

Views: 1794

Answers (4)

Mohammedshafeek C S
Mohammedshafeek C S

Reputation: 1943

To get as pair use, 

SELECT CONCAT(A,'-',B) AS Duplicate Pair,COUNT(A) AS COUNT FROM `test` GROUP BY A,B HAVING COUNT(A) > 1;

OR To get as individual use,

SELECT A AS Duplicate1,B AS Duplicate2,COUNT(A) AS COUNT FROM `test` GROUP BY A,B HAVING COUNT(A) > 1;

Upvotes: 1

sagi
sagi

Reputation: 40481

If I understood you correctly, if there had been another row in your example (2,3) then the count should be 4. If that so, use this:

SELECT 'Duplicates' as warning,sum(cnt) FROM (
    SELECT a,b,COUNT(*) as cnt
    FROM YourTable
    GROUP BY a,b
    HAVING count(*) > 1
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can use a subquery for this:

select 'Duplicates' as warning, count(*) as rowcount
from (select a, b, count(*) as cnt
      from test t
      group by a, b
      having cnt > 1
     ) t;

Upvotes: 0

AlexT82
AlexT82

Reputation: 1124

SELECT A, B, COUNT(*) as Count FROM Test GROUP BY A, B HAVING COUNT(*) > 1

Upvotes: 1

Related Questions