SQL Query Trouble: "Invalid use of group function"

Question

Note: I know the query is not complete, I am trying to piece it together a little bit at a time. I ran into the error at this point

Basically, I am getting the MySQL error

Invalid use of group function

and I can't figure out why.

This is a diagram of the database I am querying:

The question says:

"Show the publisher name and count of books published for those publishers that have published more than two books. Records should be sorted by the count of books published in descending order, followed by publisher name in ascending order. Your output should match the display given below (including column headings)."

The output is supposed to look like:

This is the query I am trying:

select publisher.name as "Publisher Name", count(book.isbn)
from publisher as "Book Count"
join book using (publisherID)
where count(book.isbn) > 2
group by publisher.name;

Answer 1

You can't use COUNT in where but only in SELECT. Try using HAVING with conditions on the group.

Answer 2

Try

SELECT MIN(p.name) AS `Publisher Name`, COUNT(b.isbn) AS `Book Count`
  FROM publisher AS p INNER JOIN 
       book      AS b ON p.PublisherID = b.PublisherID
GROUP BY p.publisherID
HAVING COUNT(b.isbn) > 2

Here is sqlfiddle

Answer 3

The problem is in your from statement. The correct syntax is:

select publisher.name as "Publisher Name", count(book.isbn)  as "Book Count"
from publisher join book
     using (publisherID)
group by publisher.name;

   having count(book.isbn) > 2

You were giving the publisher table an alias of Book Count, instead of the count.

Answer 4

2 issues:

1. You've added the alias to the table, not the column.

2. You can't use COUNT in a WHERE condition. You need to use HAVING.

Try the following:

SELECT
    publisher.name as `Publisher Name`,
    COUNT(book.isbn) as `Book Count`
FROM
    publisher 
JOIN
    book USING (publisherID)
GROUP BY
    publisher.name
HAVING
    `Book Count` > 2