pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc Howard Zuckman <marc@fallon.classyad.com>
To: William D. McCoy <wdmccoy@geo.umass.edu>
Cc: pgsql-sql@postgreSQL.org
Subject: [SQL] problem with 'insert into...': ADDITIONAL EXAMPLES
Date: Fri, 3 Jul 1998 11:36:59 -0400 (EDT)
Message-ID: <Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com> (raw)
In-Reply-To: <199806231925.PAA15195@aeolus.geo.umass.edu>

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!				      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_




Message-ID: <Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com>
Permalink:  ../Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com/
Also on:    postgresql.org/message-id/Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: marc@fallon.classyad.com, wdmccoy@geo.umass.edu
  Subject: Re: [SQL] problem with 'insert into...': ADDITIONAL EXAMPLES
  In-Reply-To: <Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com>
  References: <199806231925.PAA15195@aeolus.geo.umass.edu> <Pine.LNX.3.95.980703112007.22879A-100000@fallon.classyad.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox