Reputation: 559
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;
Upvotes: 1
Views: 178
Reputation: 1
You can't use COUNT in where but only in SELECT. Try using HAVING with conditions on the group.
Upvotes: 0
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
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
Reputation: 12836
2 issues:
You've added the alias to the table, not the column.
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