CODEWITHSUNDEEP
mysqlsqlmariadb
Alex Vieriu
Alex Vieriu

Reputation: 35

How to COUNT duplicates in a DB and return them?

I would like to ask for an advice on the best way for solving my problem.

I have a problem figuring out how to count duplicates in table like below

x       y  
1       a 
1       c
2       e 
2       g 
2       y

I would like to have number of duplicates and if possible to add info in extra columns such as:

result:

Row 1   Row 2   Row 3  Row 4 Row 5
1       a       c       -     2 
2       e       g       y     3

The problem is complex and there are a lot of other factors that need to be considered but I just need to understand this part as I don't really know where to start.

Any help as to what to search exactly for would be greatly appreciated.

Thank you!

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use group_concat() to bring all the values into a single column. Perhaps this works for your purposes:

select x, group_concat(y), count(*)
from t
group by x;

Upvotes: 1

Related Questions