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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005