0

I want to see all rows where I have duplicated entries. But I do not see my failure. Does anybody know how I can solve this problem? - SQL says: Unknown field cnt in where clause.

I also tried = 0 or even without the counter and the where condition but then it is working. And only without the where condition I also see cnt in my table but. Where is the error?

SELECT `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category` Count(*) as cnt 
FROM `wp_all_import_xml`
WHERE cnt > 1
GROUP BY `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category`

Picture: enter image description here

Greetings and Thank You!

Siyual
  • 15,882
  • 6
  • 40
  • 58
Jan
  • 257
  • 1
  • 5
  • 13

3 Answers3

1

Use HAVING instead:

SELECT `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category` Count(*) as cnt 
FROM `wp_all_import_xml`
GROUP BY `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category`
HAVING Count(*) > 1
Siyual
  • 15,882
  • 6
  • 40
  • 58
1

you should use having clauses

SELECT `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category` Count(*) as cnt 
FROM `wp_all_import_xml`
GROUP BY `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category`
having cnt > 1
denny
  • 1,914
  • 2
  • 14
  • 19
1

Groups are filtered with HAVING rows are filtere with WHERE

SELECT `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category`, Count(*) as cnt 
FROM `wp_all_import_xml`
GROUP BY `name`, `price`, `shop`, `url`, `publisher`, `platform`, `category`
HAVING cnt>1
Mihai
  • 24,788
  • 7
  • 64
  • 78