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