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

Question

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.

Do you consider duplicate rows that have all 3 numbers in any of the 3 columns? — forpas
– forpas, Commented Dec 28, 2021 at 21:20

forpas · Accepted Answer · 2021-12-28 21:24:57Z

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.

answered Dec 28, 2021 at 21:24

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MikaBA Over a year ago

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

forpas Over a year ago

@MikaBA no, this works only for numeric values.

MikaBA Over a year ago

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.

forpas Over a year ago

@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.

Cowa · Accepted Answer · 2021-12-28 21:12:41Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related