Обсуждение: "group, by", problem, when, combined, with, "insert, into"

Hi All

I'm pretty new to postgres and I'm finding soemthing baffling.  I can
perform the following query without a problem -

select loser,count(*) from moves group by loser;

but when i combine it with an insert into -
insert into losses select loser,count(*) from moves group by loser;

I get
ERROR:  Illegal use of aggregates or non-group column in target list

Am I doing something wrong or is it not possible to combine insert into and
group by.

I'm using PostgreSQL 6.5.0

Cheers
Sam


_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com

On Wed, 4 Aug 1999, sam smith wrote:

> select loser,count(*) from moves group by loser;
>
> but when i combine it with an insert into -
> insert into losses select loser,count(*) from moves group by loser;
>
> I get
> ERROR:  Illegal use of aggregates or non-group column in target list

I think you want 'select loser, count(*) into losses from moves group by
loser'

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Keep Cool, but Don't Freeze
        - Hellman's Mayonnaise

On Thu, Aug 05, 1999 at 01:19:10PM -0400, Brett W. McCoy wrote:
> On Wed, 4 Aug 1999, sam smith wrote:
>
> > select loser,count(*) from moves group by loser;
> >
> > but when i combine it with an insert into -
> > insert into losses select loser,count(*) from moves group by loser;
> >
> > I get
> > ERROR:  Illegal use of aggregates or non-group column in target list
>
> I think you want 'select loser, count(*) into losses from moves group by
> loser'
>

This may in fact be the work around, but Sam probably has a preexisting
table called losses, which the SELECT INTO syntax wont allow. I think this
bug has been brought up recently, I'll check the TODO. Ah here it is, or
at least, a couple of related entries:

-INSERT ... SELECT ... GROUP BY groups by target columns not source columns
redesign INSERT ... SELECT to have two levels of target list

So Sam, I think it's a bug. The work around is to what Brett suggests,

SELECT INTO temp_losses GROUP BY ..., then

INSERT INTO losses SELECT * from temp_losses;

Ross

--
 J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005