CODEWITHSUNDEEP
mysqlsql
Ethan Allen
Ethan Allen

Reputation: 14835

Unknown column error in this COUNT MySQL statement?

Error is

Unknown column 'num' in 'where' clause

SELECT COUNT(*) AS num, books_bookid
FROM bookgenre_has_books
WHERE num > 10
GROUP BY books_bookid

What am I doing wrong? Thanks.

Upvotes: 5

Views: 3465

Answers (4)

Siby Sunny
Siby Sunny

Reputation: 752

Try this 

`SELECT COUNT(*) , books_bookid
 FROM bookgenre_has_books
 GROUP BY books_bookid
 having count(*) > 10`

Upvotes: 2

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try this, you should use the HAVING clause

SELECT COUNT(*) AS num, books_bookid
FROM bookgenre_has_books
GROUP BY books_bookid
HAVING COUNT(*) > 10

The SQL HAVING clause is used in combination with the SQL GROUP BY clause. It can be used in an SQL SELECT statement to filter the records that a SQL GROUP BY returns.

Upvotes: 3

Anjali
Anjali

Reputation: 1718

We can write like this

    SELECT COUNT(*) , books_bookid
   FROM bookgenre_has_books
   GROUP BY books_bookid
   having count(*) > 10

you are checking for duplicates more then for that column books_bookid

Upvotes: 1

Mihai
Mihai

Reputation: 26784

WHERE clause cant see aliases,use HAVING.

It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Upvotes: 11

Related Questions