At 01:05 AM 6/09/2004 +0000, you wrote:

> > 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
>
>Thanks Helen. Simple when you know how, huh? :-) Could you please
>explain why the extra group-bys are necessary? They seem somewhat
>redundant in this example.

Redundant, maybe, but conformant with standards. Your original query would
have worked in InterBase and some versions of Firebird 1.0...

>I'd assume if I was selecting a further 10 columns, I would also
>require the 10 additions to the group-by clause?

Yes.

>Not quite sure I understand this one.

The rule is that all columns in the input set must be groupable in the
output set. The objective, of course, is to ensure that you end up with
the set you expected. Without enforcement of the grouping rule, you can
end up with grouped output that is logically wrong, even though the query
itself "works". If you then go on to use the grouped output as input to
another operation, on the assumption that it is sound, you will permutate
logical integrity errors in undetectable ways.

With a a bit of luck, Arno will have returned from his holidays and can
explain in detail how non-enforcement of the rule can stuff up integrity.

./heLen