Skip site navigation (1) Skip section navigation (2)

Re: [SQL] prob with aggregate and group by - returns multiples

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: George Dau <gedau(at)isa(dot)mim(dot)com(dot)au>
Cc: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgreSQL(dot)org>
Subject: Re: [SQL] prob with aggregate and group by - returns multiples
Date: 2000-02-28 07:50:01
Message-ID: 21290.951724201@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
George Dau <gedau(at)isa(dot)mim(dot)com(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Jan WieckDate: 2000-02-28 07:56:22
Subject: Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
Previous:From: Jan WieckDate: 2000-02-28 07:47:21
Subject: Re: [HACKERS] update_pg_pwd trigger does not work very well

pgsql-sql by date

Next:From: Tom LaneDate: 2000-02-28 16:26:35
Subject: Re: [HACKERS] Re: missing function datetime()
Previous:From: Tom LaneDate: 2000-02-28 07:38:03
Subject: Re: [SQL] SQL query problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group