SQL error - please help.

From: Bernard <bht(at)actrix(dot)gen(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: SQL error - please help.
Date: 2005-08-22 12:24:53
Message-ID: qhejg11pcfmknb3r94a75md3cjeo4hhdb0@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

... End of testcase

I have looked up this error message in the mailing list archives and
found a case with a plausible explanation (ambiguity) but I can't see
how this explanation would apply to the case under discussion.

Any help would be highly appreciated.

Regards

Bernard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gnanavel S 2005-08-22 12:25:06 Re: Rules UPDATE
Previous Message Bruno Almeida do Lago 2005-08-22 11:55:00 OCFS released as GPL