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.

Use GROUP BY .. HAVING COUNT, a self-join, or a nested select. — user2864740, Apr 11 '14 at 23:01
http://stackoverflow.com/questions/301793/mysql-using-count-in-the-where-clause?rq=1 — user2864740, Apr 11 '14 at 23:04

score 6 · Accepted Answer · answered Apr 11 '14 at 23:03

6

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;

answered Apr 11 '14 at 23:03

Ravinder Reddy

23,692
6
52
82

1

@EricImprint Hmm, another MySQL oddity (or at least difference) strikes me. – user2864740 Apr 11 '14 at 23:06
It works. Better practice is not to use [*Reserved words*](https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) like `count` as identifiers, though it is an acceptable with no *back ticks* – Ravinder Reddy Apr 11 '14 at 23:07

Kraang Prime · Answer 2 · 2014-04-11T23:10:29.940

1

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;

edited Apr 11 '14 at 23:10

answered Apr 11 '14 at 22:59

Kraang Prime

9,981
10
58
124

I am getting a 1054 error: Unknown column COUNT in where clause. – EricImprint Apr 11 '14 at 23:01
Why not ?. Oh... hahaha... i see XD --- need to rename the field... also, don't use `AS` ... just space. – Kraang Prime Apr 11 '14 at 23:01
1

@SanuelJackson Not yet. "In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause." see [manual](http://dev.mysql.com/doc/refman/5.5/en/select.html) – VMai Apr 11 '14 at 23:08

score 1 · Answer 3 · answered Apr 11 '14 at 23:04

1

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;

answered Apr 11 '14 at 23:04

Leo

283
2
4

score 0 · Answer 4 · answered Apr 11 '14 at 23:02

0

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;

answered Apr 11 '14 at 23:02

JoeC

1,850
14
12

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

4 Answers4