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

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