CODEWITHSUNDEEP
mysqlcount
EricImprint
EricImprint

Reputation: 177

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

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.

Upvotes: 3

Views: 11205

Answers (4)

Kraang Prime
Kraang Prime

Reputation: 10479

Try adding DISTINCT to the query

SELECT * FROM (
SELECT DISTINCT `Id`, (SELECT COUNT(Id) FROM `testproductdata` WHERE `Id`=`Id`) `count`
FROM `testproductdata`
GROUP BY `Id`
HAVING `count` > 1
)
ORDER BY `count` DESC;

Upvotes: 1

Leo
Leo

Reputation: 293

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;

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

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;

Upvotes: 6

JoeC
JoeC

Reputation: 1850

Try this out

select * from 
(SELECT `Id`, COUNT(*) AS `c`
FROM `testproductdata`
group by `Id`)
WHERE `c` > 1
ORDER BY `c` DESC;

or

SELECT `Id`, COUNT(*) AS `c`
FROM `testproductdata`
group by `Id`
having `c` > 1
ORDER BY `c` DESC;

Upvotes: 0

Related Questions