Subject Re: [firebird-support] Help with query - Invalid expression in the select list
Author Helen Borrie
At 11:10 PM 5/09/2004 +0000, you wrote:
>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
>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?
>categories c
>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:
categories c
LEFT JOIN books_to_categories x
ON c.categoryid = x.categoryid
GROUP BY c.categoryid,