From: | "Ricardo Coelho" <rcoelho(at)px(dot)com(dot)br> |
---|---|
To: | "Ed Loehr" <ELOEHR(at)austin(dot)rr(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] Non-group columns with aggregate functions |
Date: | 1999-12-22 20:48:56 |
Message-ID: | 002601bf4cbd$f817c320$39fafdc8@px.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Ed,
The query works now. Thanks.
Thanks everybody.
----- Original Message -----
From: Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com>
To: Ricardo Coelho <rcoelho(at)px(dot)com(dot)br>
Cc: <pgsql-hackers(at)postgreSQL(dot)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
>
>
> ************
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Parks | 1999-12-22 20:55:13 | Re: [HACKERS] Non-group columns with aggregate functions |
Previous Message | Bruce Momjian | 1999-12-22 20:21:37 | Re: [HACKERS] Non-group columns with aggregate functions |