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
  • 16,415
  • 8
  • 44
  • 58
Jan
  • 277
  • 1
  • 6
  • 16

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
  • 16,415
  • 8
  • 44
  • 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
  • 2,084
  • 2
  • 15
  • 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
  • 26,325
  • 7
  • 66
  • 81