djmordigal
djmordigal

Reputation: 559

SQL Query Trouble: "Invalid use of group function"

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:

diagram

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:

output

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;

Upvotes: 1

Views: 178

Answers (4)

Longo Stefano
Longo Stefano

Reputation: 1

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

Upvotes: 0

peterm
peterm

Reputation: 92845

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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.

Upvotes: 0

Michael
Michael

Reputation: 12836

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

Upvotes: 0

Related Questions