Finding duplicate values in multiple colums in a SQL table and count-CodePudding

I have the following table structure:

id num1 num2 num3  ...
 1    1    2    3
 2    1    3    2
 3    1    2    3
 .
 .
 .

I use the following command to display the duplicates and the counts:

SELECT COUNT(num1), num1, num2, num3 
FROM table 
GROUP BY num1, num2, num3 
HAVING (COUNT(num1) > 1) AND (COUNT(num2) > 1) AND (COUNT(num3) > 1)

This command gives me a count of 2. I would like to know how the second line could also be counted as a dublicate.

CodePudding user response：

You need the scalar functions MIN() and MAX() to get the 3 integer values of each row in ascending order, so that you can create a unique triplet to group by:

SELECT COUNT(*) count,
       MIN(num1, num2, num3) num_1,
       num1   num2   num3 - MIN(num1, num2, num3) - MAX(num1, num2, num3) num_2,
       MAX(num1, num2, num3) num_3
FROM tablename 
GROUP BY num_1, num_2, num_3 
HAVING COUNT(*) > 1;

See the demo.

CodePudding user response：

changing operator to OR will return what you want

SELECT COUNT(num1), num1, num2, num3 
FROM table 
GROUP BY num1, num2, num3 
HAVING (COUNT(num1) > 1) OR (COUNT(num2) > 1) OR (COUNT(num3) > 1)