Re: SQL error - please help.

On 8/22/05 8:24 AM, "Bernard" <bht(at)actrix(dot)gen(dot)nz> wrote:

> Dear Postgresql specialists
>
> I would like to seek help with a SQL query that was developed and
> tested with other SQL92 compliant databases.
>
> Please examine the following testcase and the result that I get:
>
> # su postgres
> $ psql -d mydb -U postgres
> CREATE TABLE DEPARTMENT(PK INT NOT NULL, NAME TEXT NOT NULL);
> ALTER TABLE DEPARTMENT ADD PRIMARY KEY(PK);
> CREATE TABLE PROJECT(PK INT NOT NULL, DEPARTMENT_FK INT NOT NULL, NAME
> VARCHAR(30) NOT NULL, VALUE INT NOT NULL);
> ALTER TABLE PROJECT ADD PRIMARY KEY(PK);
> INSERT INTO DEPARTMENT(PK,NAME)VALUES(1,'Human Resources');
> INSERT INTO DEPARTMENT(PK,NAME)VALUES(2,'Tax');
> INSERT INTO
> PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(1,1,'Head-Hunt',1000);
> INSERT INTO
> PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(2,1,'Redundancy',100);
> INSERT INTO
> PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(3,2,'Avoidance',1000);
> INSERT INTO
> PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(4,2,'Charity',100);
> INSERT INTO
> PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(5,2,'Lobbying',10000);
>
> SELECT
> DEPARTMENT.PK,
> DEPARTMENT.NAME,
> MIN(PROJECT.VALUE)AS RATING
> FROM DEPARTMENT,
> PROJECT
> WHERE DEPARTMENT.PK=PROJECT.DEPARTMENT_FK
> GROUP BY DEPARTMENT.PK
> ORDER BY DEPARTMENT.PK;
>
> ERROR: column "department.name" must appear in the GROUP BY clause or
> be used in an aggregate function

It simply means that you must include department.name in the group by clause
like:

GROUP BY department.pk, department.name

The rest of the query looks OK.

Sean