Received: from Fallon.classyad.com (root@classyad.com [152.160.43.1]) by hub.org (8.8.8/8.7.5) with ESMTP id LAA29474 for ; Fri, 3 Jul 1998 11:40:27 -0400 (EDT) Received: from localhost (IDENT:marc@localhost [127.0.0.1]) by Fallon.classyad.com (8.9.0/8.9.0) with SMTP id LAA22931; Fri, 3 Jul 1998 11:37:00 -0400 Date: Fri, 3 Jul 1998 11:36:59 -0400 (EDT) From: Marc Howard Zuckman To: "William D. McCoy" cc: pgsql-sql@postgreSQL.org Subject: [SQL] problem with 'insert into...': ADDITIONAL EXAMPLES In-Reply-To: <199806231925.PAA15195@aeolus.geo.umass.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 23 Jun 1998, William D. McCoy wrote: > I am trying to work around the lack of an outer join function in > postgreSql. I am trying to construct a full outer join of two > identically defined tables, each of which contains (along with other > data), a value for a particular lab sample. > > I have come across the following problem. When I execute the > following query it returns the expected result (816 rows returned). > > select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values > from free_ratios f > where not exists ( > select * > from hyd_ratios h > where h.lab_no = f.lab_no > and h.prep_no = f.prep_no > and h.run_no = f.run_no > ) > group by f.peak_values, f.lab_no, f.prep_no; > > > However, when I add an 'insert into table_name' to the query like > this: > > insert into both_ratios_avg > select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values > from free_ratios f > where not exists ( > select * > from hyd_ratios h > where h.lab_no = f.lab_no > and h.prep_no = f.prep_no > and h.run_no = f.run_no > ) > group by f.peak_values, f.lab_no, f.prep_no; > > > I get the following error message: > > ERROR: parser: aggregates not allowed in GROUP BY clause > > > There are clearly no aggregates in my GROUP BY clause. > > > I get the same error message with the following query, although, just > like the example above, the select by itself works fine: > > > insert into both_ratios_avg > select f.lab_no, f.prep_no, avg(fai), avg(hai), f.peak_values > from free_ratios f, hyd_ratios h > where f.lab_no = h.lab_no > and f.prep_no = h.prep_no > and f.run_no = h.run_no > group by f.peak_values, f.lab_no, f.prep_no; > > > Just to make things more interesting, the following very similar query > works fine and does not give any error: > > insert into both_ratios_avg > select h.lab_no, h.prep_no, NULL, avg(hai), h.peak_values > from hyd_ratios h > where not exists ( > select * > from free_ratios f > where f.lab_no = h.lab_no > and f.prep_no = h.prep_no > and f.run_no = h.run_no > ) > group by h.peak_values, h.lab_no, h.prep_no; > > > (This is another third of my full outer join workaround.) Any ideas > about what might be happening here? > I don't know where the bug is, but I have similar results with the following: insert into historical_mv(symbol, mv, d) select s.symbol, sum(%(lw.price * s.shares)), lw.d from stock s, quotes lw, timeinterval where s.symbol=lw.tick and lw.d=timeinterval.lastweek and s.acquisition_date <= lw.d and s.market_index=false group by s.symbol, lw.d ; ERROR: parser: aggregates not allowed in GROUP BY clause and a different, but possibly related error with this modification: insert into historical_mv(symbol, mv, d) select s.symbol, sum(%(lw.price * s.shares)), timeinterval.lastweek from stock s, quotes lw, timeinterval where s.symbol=lw.tick and lw.d=timeinterval.lastweek and s.acquisition_date <= lw.d and s.market_index=false group by s.symbol, timeinterval.lastweek ; ERROR: The field being grouped by must appear in the target list Speculating that the target list might by interpreted as (symbol, mv, d), I attempt the following with the same results: insert into historical_mv(symbol, mv, d) select s.symbol, sum(lw.price * s.shares), timeinterval.lastweek from stock s, quotes lw, timeinterval where s.symbol=lw.tick and lw.d=timeinterval.lastweek and s.acquisition_date <= lw.d and s.market_index=false group by symbol, d ; ERROR: parser: aggregates not allowed in GROUP BY clause and deleting this target list still doesn't solve the problem. insert into historical_mv select s.symbol, sum(lw.price * s.shares), timeinterval.lastweek from stock s, quotes lw, timeinterval where s.symbol=lw.tick and lw.d=timeinterval.lastweek and s.acquisition_date <= lw.d and s.market_index=false group by s.symbol, timeinterval.lastweek ; ERROR: The field being grouped by must appear in the target list Marc Zuckman marc@fallon.classyad.com _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ _ Visit The Home and Condo MarketPlace _ _ http://www.ClassyAd.com _ _ _ _ FREE basic property listings/advertisements and searches. _ _ _ _ Try our premium, yet inexpensive services for a real _ _ selling or buying edge! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_