Find duplicates for combination of two columns in a MySQL database

count, distinct, duplicates, mysql, select

Solution

This works even without subselects.

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

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

Problem

I work 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` there is one or two different texts in the info column. And those are the entries I tried to find. I tried the following ``` SELECT COUNT(DISTINCT(`pile`, `position`)) FROM db; ``` But received an error message ``` ERROR 1241 (21000): Operand should contain 1 column(s) ``` Is there a way to find distinct combinations of values in two columns?

Original source