1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
MikaBA
  • 57
  • 4

2 Answers2

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • That does exactly what it should. Thank you. Does that also work with CHAR instead of Int? I have a similar use case for names of people – MikaBA Dec 28 '21 at 21:46
  • @MikaBA no, this works only for numeric values. – forpas Dec 28 '21 at 21:47
  • do you have another idea for char or should i ask a new question? Sorry for the circumstances. These are both topics that have stolen me a few hours. – MikaBA Dec 28 '21 at 21:51
  • @MikaBA the requirement or the solution is different for non-numeric values, so you should ask a new question where you explain what you want with sample data and expected results. – forpas Dec 28 '21 at 21:55
0

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)
Cowa
  • 1
  • 2