Re: field must appear in the GROUP BY clause or be used

Bill Moran said:
> Like I said, the most important part (to me) is to understand why
> Postgres refuses to run this.  The fact that I don't know why points
> to an obvious lack of understanding on my account, and I'd like to
> remedy that :D

I have always assumed that you had to place all (non aggregated) columns
in your select in the "group by" clause as well. I suspect that the other
database isn't so picky (or is incorrect?).

Presumably changing the query to:
  select id, name from gov_capital_project group by id, name order by name;
works fine?

> To that effect, if anyone can point me to a doc that will help me
> gain a better understanding of why this error occurs, I'd be happy
> to read it!

Have a look at:
http://www.postgresql.org/docs/7.4/static/sql-select.html#SQL-GROUPBY

[excerpted text]

GROUP BY Clause

The optional GROUP BY clause has the general form

GROUP BY expression [, ...]

GROUP BY will condense into a single row all selected rows that share the
same values for the grouped expressions. expression can be an input column
name, or the name or ordinal number of an output column (SELECT list
item), or an arbitrary expression formed from input-column values. In case
of ambiguity, a GROUP BY name will be interpreted as an input-column name
rather than an output column name.

Aggregate functions, if any are used, are computed across all rows making
up each group, producing a separate value for each group (whereas without
GROUP BY, an aggregate produces a single value computed across all the
selected rows). When GROUP BY is present, it is not valid for the SELECT
list expressions to refer to ungrouped columns except within aggregate
functions, since there would be more than one possible value to return for
an ungrouped column.

John Sidney-Woollett