Search Postgresql Archives

Re: SQL error - please help.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 8/22/05 8:24 AM, "Bernard" <bht@xxxxxxxxxxxxx> 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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux