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: prob with aggregate and group by - returns multiples
Date: Mon, 28 Feb 2000 16:22:46 +1000
Message-ID: <69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au> (raw)

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



view thread (24+ messages)  latest in thread

Message-ID: <69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au>
Permalink:  ../69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au/
Also on:    postgresql.org/message-id/69D6F577E4ADD311ABE600805FFE80580EBCF9@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: prob with aggregate and group by - returns multiples
  In-Reply-To: <69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au>
  References: <69D6F577E4ADD311ABE600805FFE80580EBCF9@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