Help with query - Invalid expression in the select list

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

Try the group by clause:
GROUP BY c.categoryid, c.name

If you combine in the select an aggregate function (count, sum, max) together with 'normal' colums (id, name, etc.), all the normal columns have to be in the group by clause.

Peter

Thanks Peter. Seems a little obvious now looking back at the error message! Oh well, hindsight and all that. Cheers.

Happens to all of us now and then!