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

prob with aggregate and group by - returns multiples

From: George Dau <gedau(at)isa(dot)mim(dot)com(dot)au>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgreSQL(dot)org>
Subject: prob with aggregate and group by - returns multiples
Date: 2000-02-28 06:22:46
Message-ID: 69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
I have a group by that groups some, but not all, identical rows. Here are
the details:
I can reproduce the problem using one single table, details below;

Table    = hits
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| userid                           | varchar                          |
12 |
| dat                              | date                             |
4 |
| tim                              | time                             |
8 |
| ipa                              | int4                             |
4 |
| ipb                              | int4                             |
4 |
| ipc                              | int4                             |
4 |
| ipd                              | int4                             |
4 |
| site                             | varchar                          |
50 |
+----------------------------------+----------------------------------+-----
--+

I want a report showing how many occurrences of "site" there are for each
distinct "site".
There is a lot of data, in there, so I'll look at a particular example of
the problem. When
I run this query:

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?

On a lesser note: I tried "select * into temp from hits" as per the doco,
but it barfs. Looks
like the syntax has changed. Any current "railway" diagrams for the commands
anywhere? Also,
where can I find out about other environment settings like PGDATESTYLE? I
know about that one,
but what others are there?

I'm trying to port from Oracle. Any guides? There are many differences in
the SQL.


**************************************************************
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(at)mim(dot)com(dot)au or phone: Australia 07 3833 8042.
**************************************************************

Responses

pgsql-hackers by date

Next:From: Lamar OwenDate: 2000-02-28 06:36:58
Subject: Syslog and pg_options (for RPMs)
Previous:From: subaesh ramjanDate: 2000-02-28 05:54:09
Subject: info

pgsql-sql by date

Next:From: Tom LaneDate: 2000-02-28 07:38:03
Subject: Re: [SQL] SQL query problem
Previous:From: Vikrant RathoreDate: 2000-02-28 05:40:21
Subject: SQL query problem

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