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.
**************************************************************
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