Thread

1. "group, by", problem, when, combined, with, "insert, into"

   sam smith <sam_smith20@hotmail.com> — 1999-08-04T21:35:19Z

   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
   
   

2. Re: [GENERAL] "group, by", problem, when, combined, with, "insert, into"

   Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 1999-08-05T14:01:37Z

   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
   
   

3. Re: [GENERAL] "group, by", problem, when, combined, with, "insert, into"

   Brett W. McCoy <bmccoy@lan2wan.com> — 1999-08-05T17:19:10Z

   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