Postgres Pro
Downloads
Home   >   mailing lists

Thread: Non-group columns with aggregate functions

Non-group columns with aggregate functions

From
"Ricardo Coelho"
Date:
Hi All,

I had seen the answer for my question before, but hacker's E-mail search
isn't finding any word.

I'm using PgSQL 6.5.2 with RHLinux 6.0.

How can I use non-group columns in a select with aggregate functions ? To
me, the following query makes sense.

teste=> create table people(pp_id int2 primary key, pp_name text);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'people_pkey'
for table 'people'
CREATE
teste=> create table workpgsql(wp_people int2, wp_date date, hoursofwork
int2);
CREATE
teste=> insert into people values (1,'ME');
INSERT 226808 1
teste=> insert into people values (2,'YOU');
INSERT 226809 1
teste=> insert into workpgsql values (1,'01/01/2000',5);
INSERT 226810 1
teste=> insert into workpgsql values (1,'01/01/2000',4);
INSERT 226811 1
teste=> insert into workpgsql values (2,'01/01/2000',6);
INSERT 226812 1
teste=> insert into workpgsql values (2,'01/01/2000',6);
INSERT 226813 1
teste=> select pp_name,wp_date,sum(hoursofwork) from people,workpgsql
teste-> where pp_id=wp_people
teste-> group by wp_people,wp_date;
ERROR:  Illegal use of aggregates or non-group column in target list

If anybody knows how to rebuild this query to work, thanks in advance.

Thanks,

Ricardo Coelho.

Re: [HACKERS] Non-group columns with aggregate functions

From
Ed Loehr
Date:
Ricardo Coelho wrote:

> How can I use non-group columns in a select with aggregate functions ? To
> me, the following query makes sense.
>
> teste=> create table people(pp_id int2 primary key, pp_name text);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'people_pkey'
> for table 'people'
> CREATE
> teste=> create table workpgsql(wp_people int2, wp_date date, hoursofwork
> int2);
> CREATE
> teste=> insert into people values (1,'ME');
> INSERT 226808 1
> teste=> insert into people values (2,'YOU');
> INSERT 226809 1
> teste=> insert into workpgsql values (1,'01/01/2000',5);
> INSERT 226810 1
> teste=> insert into workpgsql values (1,'01/01/2000',4);
> INSERT 226811 1
> teste=> insert into workpgsql values (2,'01/01/2000',6);
> INSERT 226812 1
> teste=> insert into workpgsql values (2,'01/01/2000',6);
> INSERT 226813 1
> teste=> select pp_name,wp_date,sum(hoursofwork) from people,workpgsql
> teste-> where pp_id=wp_people
> teste-> group by wp_people,wp_date;
> ERROR:  Illegal use of aggregates or non-group column in target list
>
> If anybody knows how to rebuild this query to work, thanks in advance.

Non-aggregated columns must appear in the group by clause.

select pp_name, wp_date, sum(hoursofwork)
from people, workpgsql
where pp_id=wp_people
group by pp_name,wp_date;

Cheers.
Ed Loehr

Re: [HACKERS] Non-group columns with aggregate functions

From
Bruce Momjian
Date:
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hi All,
> 
> I had seen the answer for my question before, but hacker's E-mail search
> isn't finding any word.
> 
> I'm using PgSQL 6.5.2 with RHLinux 6.0.
> 
> How can I use non-group columns in a select with aggregate functions ? To
> me, the following query makes sense.
> 
> teste=> create table people(pp_id int2 primary key, pp_name text);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'people_pkey'
> for table 'people'
> CREATE
> teste=> create table workpgsql(wp_people int2, wp_date date, hoursofwork
> int2);
> CREATE
> teste=> insert into people values (1,'ME');
> INSERT 226808 1
> teste=> insert into people values (2,'YOU');
> INSERT 226809 1
> teste=> insert into workpgsql values (1,'01/01/2000',5);
> INSERT 226810 1
> teste=> insert into workpgsql values (1,'01/01/2000',4);
> INSERT 226811 1
> teste=> insert into workpgsql values (2,'01/01/2000',6);
> INSERT 226812 1
> teste=> insert into workpgsql values (2,'01/01/2000',6);
> INSERT 226813 1
> teste=> select pp_name,wp_date,sum(hoursofwork) from people,workpgsql
> teste-> where pp_id=wp_people
> teste-> group by wp_people,wp_date;
> ERROR:  Illegal use of aggregates or non-group column in target list
> 
> If anybody knows how to rebuild this query to work, thanks in advance.

New 7.0 error message will be:

ERROR:  Attribute people.pp_name must be GROUPed or used in an aggregate function 

Cause is that you group by wp_people, not pp_name.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: [HACKERS] Non-group columns with aggregate functions

From
"Ricardo Coelho"
Date:
Ed,

The query works now. Thanks.

Thanks everybody.

----- Original Message -----
From: Ed Loehr <ELOEHR@austin.rr.com>
To: Ricardo Coelho <rcoelho@px.com.br>
Cc: <pgsql-hackers@postgreSQL.org>
Sent: Wednesday, December 22, 1999 6:21 PM
Subject: Re: [HACKERS] Non-group columns with aggregate functions


> Ricardo Coelho wrote:
>
> > How can I use non-group columns in a select with aggregate functions ?
To
> > me, the following query makes sense.
> >
> > teste=> create table people(pp_id int2 primary key, pp_name text);
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'people_pkey'
> > for table 'people'
> > CREATE
> > teste=> create table workpgsql(wp_people int2, wp_date date, hoursofwork
> > int2);
> > CREATE
> > teste=> insert into people values (1,'ME');
> > INSERT 226808 1
> > teste=> insert into people values (2,'YOU');
> > INSERT 226809 1
> > teste=> insert into workpgsql values (1,'01/01/2000',5);
> > INSERT 226810 1
> > teste=> insert into workpgsql values (1,'01/01/2000',4);
> > INSERT 226811 1
> > teste=> insert into workpgsql values (2,'01/01/2000',6);
> > INSERT 226812 1
> > teste=> insert into workpgsql values (2,'01/01/2000',6);
> > INSERT 226813 1
> > teste=> select pp_name,wp_date,sum(hoursofwork) from people,workpgsql
> > teste-> where pp_id=wp_people
> > teste-> group by wp_people,wp_date;
> > ERROR:  Illegal use of aggregates or non-group column in target list
> >
> > If anybody knows how to rebuild this query to work, thanks in advance.
>
> Non-aggregated columns must appear in the group by clause.
>
> select pp_name, wp_date, sum(hoursofwork)
> from people, workpgsql
> where pp_id=wp_people
> group by pp_name,wp_date;
>
> Cheers.
> Ed Loehr
>
>
> ************