Subject | Help with query - Invalid expression in the select list |
---|---|

Author | russx2 |

Post date | 2004-09-05T23:10:56Z |

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

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