Find duplicates for a combination of two columns in a MySQL database

Question

I am working on a dataset with three different columns: pile , position and info .

There is no duplicate in the database, but it can happen that for one combination of pile and position in the info column there are one or two different texts. And these are the records that I tried to find.

I tried the following

 SELECT COUNT(DISTINCT(`pile`, `position`)) FROM db; 

But an error message is received

 ERROR 1241 (21000): Operand should contain 1 column(s) 

Is there a way to find different combinations of values ​​in two columns?

Answer 1

This works even without subqueries.

 SELECT `pile`, `position`, COUNT(*) AS c FROM db GROUP BY `pile`, `position` HAVING c > 1; 

The above command shows all combinations of pile and position that occur more than once in the db table.

Answer 2

To get the counter of various duplicates (the group used here by preference)

  select count(*) from ( select pile, position from db group by pile, position ) x 

To find actual duplicate entries

  select db.* from ( select pile, position from db group by pile, position having count(*) > 1 ) x join db on db.pile = x.pile and db.position = x.position 

Answer 3

 SELECT * FROM db x WHERE EXISTS ( SELECT 1 FROM db y WHERE y.pile = x.pile AND y.position =x.postion AND y.other_field <> x.other_field ); 

Now for other_field you can use a unique identifier column or any combination of fields (except {pole, postion}, of course)