From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Ricardo Coelho <rcoelho(at)px(dot)com(dot)br> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Non-group columns with aggregate functions |
Date: | 1999-12-22 20:21:37 |
Message-ID: | 199912222021.PAA01180@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
[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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Ricardo Coelho | 1999-12-22 20:48:56 | Re: [HACKERS] Non-group columns with aggregate functions |
Previous Message | Ed Loehr | 1999-12-22 20:21:21 | Re: [HACKERS] Non-group columns with aggregate functions |