Use COUNT(*) AS `COUNT` in MYSQL Where Clause

Question

I am trying to find, count and report all instances of duplicates entries in a column. Any idea how to do that similar to my attempt below.

SELECT `Id`, COUNT(*) AS `COUNT`
FROM `testproductdata`
WHERE `COUNT` > 1
GROUP BY `Id`
ORDER BY `COUNT` DESC;

I am getting a 1054 error: Unknown column COUNT in where clause.

Answer 1

Use HAVING over WHERE with GROUP BY

SELECT `Id`, COUNT(*) AS `COUNT`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `COUNT` > 1
ORDER BY `COUNT` DESC;

And I suggest to use relevant name for expression on count(*) as total_count than COUNT.

Change query as below:

SELECT `Id`, COUNT(*) AS `total_count`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `total_count` > 1
ORDER BY `total_count` DESC;

Answer 2

You should change the where for a having, this should work:

SELECT Id, COUNT(1) AS count
FROM testproductdata
GROUP BY Id
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC;