SQL error - please help.

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.