Обсуждение: SQL error - please help.

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

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

am  23.08.2005, um  0:24:53 +1200 mailte Bernard folgendes:
> 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

Add a ',department.name' to the 'GROUP BY' ->

...
PROJECT
 WHERE DEPARTMENT.PK=PROJECT.DEPARTMENT_FK
  GROUP BY DEPARTMENT.PK, department.name
 ORDER BY DEPARTMENT.PK;
...



Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On Tue, 23 Aug 2005, Bernard wrote:

> Dear Postgresql specialists
>
> I would like to seek help with a SQL query that was developed and
> tested with other SQL92 compliant databases.

IIRC, allowing select items that are not in the group by but are
functionally dependant on the group by columns was added in SQL99.

7.9 <query specification> (in the SQL92 draft) states "If T is a grouped
table, then each <column reference> in each <value expression> that
references a column of T shall reference a grouping column or be specified
within a <set function specification>" which is not the case in the query
below.

There's been talk about doing the SQL99 rules in the past, but noone's
stepped up to do it.

> 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