How to use WHERE for a custom field (alias)?

Question

How to execute such a request:

SELECT type, COUNT(name) as cnt FROM products WHERE cnt > 1 GROUP BY type 

This query causes error #1054 - Unknown column 'cnt' in 'where clause'

This is because WHERE is applied before grouping.
How can I get around this?

Table structure:

 id name type price 123451 Park Great Hits Music 19.99 123452 Silly Puddy Toy 3.99 123453 Playstation Toy 89.95 123454 Men T-Shirt Clothing 32.50 123455 Blouse Clothing 34.97 123456 Electronica 2002 Music 3.99 123457 Country Tunes Music 21.55 123458 Watermelon Food 8.73 

Table structure borrowed from http://www.tizag.com/mysqlTutorial/mysqlcount.php for simplicity.

Answer 1

You need to use the HAVING to filter records from an aggregate query:

 SELECT type, COUNT(name) as cnt FROM products GROUP BY type HAVING cnt > 0;