I'm trying to show the duplicate codes in a mysql query.
I'm doing it with this code:
SELECT 'id', 'idarticulo', 'talla', 'color', count(*) FROM 'CodigosEan' GROUP BY 'idarticulo', 'talla', 'color' HAVING count(*) > 1
The problem is that if there are duplicates, it groups them together and I do not see what they are. I would need to know the ID of all affected (or grouped) Is there any way to know?
With Inner Join (joining the codigose table with the result of your duplicate query)
SELECT tc.id, tc.idarticulo, tc.talla, tc.color
FROM CodigosEan tc
INNER JOIN
( SELECT idarticulo, talla, color, count(*)
FROM CodigosEan
GROUP BY idarticulo, talla, color
HAVING count(*) > 1
) t2
ON tc.idarticulo = t2.idarticulo and
tc.talla = t2.talla and
tc.color = t2.color
you are doing the count(*) that is to say GLOBAL , you must add the variables that you want to count when a group is fulfilled
SELECT count(idarticulo) as cantidad_repetidos,
idarticulo,
talla,
color
FROM codigosean
GROUP BY idarticulo
HAVING cantidad_repetidos > 1;