pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: George Dau <gedau@isa.mim.com.au>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: RE: [SQL] prob with aggregate and group by - returns multiples
Date: Tue, 29 Feb 2000 07:58:24 +1000
Message-ID: <69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au> (raw)

The selects below are self-contained. The one that is failing is, in full,
#!/bin/bash
#
psql web <<EOS

select count(site), site
  from hits
 group by site;

\q;
EOS

The first part of the output is:
count|site
-----+-------------------------------
    8|1.digital.cnet.com
   18|1000.stars.ru
    1|10e2.linkexchange.ru
    2|128.11.10.58
    1|192.148.121.27
    1|195.115.59.156
    7|195.172.106.37
    1|195.86.97.21

and then, finally,
    2|xbase.plugsys.com
    3|xbase.plugsys.com
    2|xbase.plugsys.com
    1|xbase.plugsys.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    3|xlink.zdnet.com
    2|xlink.zdnet.com
    1|xlink.zdnet.com
    1|y0.extreme-dm.com
    1|y0.extreme-dm.com
    2|y1.extreme-dm.com
    1|yahoo.com.au
    1|yellowpages.com.au
    2|z0.extreme-dm.com
    1|z0.extreme-dm.com
    2|z1.extreme-dm.com
    2|z1.extreme-dm.com
    1|z1.extreme-dm.com
(5510 rows)

I have postgresql version 6.2 running on RedHat Linux kernel 2.2.5  The box
has 128Meg RAM
and lotsa disk. Upgrading to 6.5 will be either expensive (download) or very
slow (buy CD
from Canada). I hope there is another solution.

The TEMP table references are in HTML doco in query.html where it describes
"select into".

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, 28 February 2000 17:50
To: George Dau
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] prob with aggregate and group by - returns multiples 


George Dau <gedau@isa.mim.com.au> writes:
> select count(site), site from hits group by site;

> The output contains lines like the following. Note that these are all
> consecutive in the output.

>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     3|xlink.zdnet.com
>     2|xlink.zdnet.com
>     1|xlink.zdnet.com

> Suspecting that there were differences in each xlink.zdnet.com, I counted
> just them:

> web=> select count (*) from hits where site='xlink.zdnet.com';
> count
> -----
>    22
> (1 row)

> So, all 22 xlink.zdnet.com are selected above, but they have not grouped
in
> the previous one. Any ideas why?

Wow, that is bizarre.  My first thought was that you had varying numbers
of trailing blanks in the "site" values, but your second example seems
to disprove that theory.

What Postgres version are you running, and on what platform?  Can you
generate a self-contained example (a script that demonstrates the error
from a standing start)?  I suspect you may be hitting a platform-
specific porting problem, but it's just speculation unless we have a
self-contained test case to try on other machines.

> On a lesser note: I tried "select * into temp from hits" as per the doco,
> but it barfs.

Postgres thinks that TEMP is a keyword, so it won't take it as a table
name unless you put quotes around it.  If we have doco examples that
use TEMP as a table name, they need to be changed --- do you recall
where you saw that, exactly?

			regards, tom lane


**************************************************************
The information contained in this E-Mail is confidential
and is intended only for the use of the addressee(s).
If you receive this E-Mail in error, any use, distribution
or copying of this E-Mail is not permitted.  You are 
requested to forward unwanted E-Mail and address any problems
to the MIM Holdings Limited Help Desk. 
E-Mail: helpdesk@mim.com.au or phone: Australia 07 3833 8042.
**************************************************************



view thread (24+ messages)  latest in thread

Message-ID: <69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au>
Permalink:  ../69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au/
Also on:    postgresql.org/message-id/69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au

 · 

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: gedau@isa.mim.com.au
  Subject: Re: [SQL] prob with aggregate and group by - returns multiples
  In-Reply-To: <69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au>
  References: <69D6F577E4ADD311ABE600805FFE80580EBCFB@isaexch.isa.mim.com.au>

* 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