Hello,

As an excercise I am trying to port a simple mysql book-library app to
firebird (1.5). I am having trouble with the following query. It
should return a list of book categories and how many books are in each
category.

There is a 'category' table which includes info such as the name of
the category etc. There is also a books_to_categories table which
simply cross references books to categories (categoryID | bookID).

In mysql, the following query gets a list of category names and ids,
as well as how many books are in each category. It works by COUNTing
the number of rows in the cross-reference table that contain each
category id (and since each matching row is a link to a book, gives
the total number of books in this category). However, in firebird, I
get the following error:

"Invalid expression in the select list (not contained in either an
aggregate function or the GROUP BY clause)."

Could anyone give me a clue as to what I'm doing wrong?

SELECT
c.categoryid,
c.name,
COUNT(x.categoryid)
FROM
categories c
LEFT JOIN
books_to_categories x
ON c.categoryid = x.categoryid
GROUP BY c.categoryid

Any pointers/advice would be very much appreciated. Been wrestling
with this one for a while!

Thanks,
Russ