Обсуждение: Non-group columns with aggregate functions
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.
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
[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
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 > > > ************