CODEWITHSUNDEEP
mysql
user1673591
user1673591

Reputation: 79

How to find duplicates inside a group in group by in mysql?

I need to find the column 3 value if column 1 values are same for all within the group when grouped by column 3.

I tried

select column_3 from table group by column_3 having count(column_3) = count(column_1);

+-----------+---------+-----------+
| Column_1  | Column_2| Column_3  |
+-----------+---------+-----------+
| A         | B       | 1         |
| A         | C       | 1         |
| D         | E       | 2         |
| A         | E       | 1         |
| F         | G       | 2         |
+-----------+---------+-----------+

Expected Value
Column 3
1

Since when grouped by column 3, all values of column 1 are 'A'.
2 wont be the result because column 1 values are different D and F.

Upvotes: 0

Views: 35

Answers (1)

forpas
forpas

Reputation: 164069

Change the condition in the having clause:

select column_3 
from table 
group by column_3 
having min(column_1) = max(column_1);

Or:

select column_3 
from table 
group by column_3 
having count(distinct column_1) = 1;

Upvotes: 2

Related Questions