At 11:10 PM 5/09/2004 +0000, you wrote:

>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!

Just include all of the grouping columns in the column list:
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, c.name

./heLen