Received: from mimhimta.mim.com.au ([203.3.101.35]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA62126 for ; Mon, 28 Feb 2000 01:23:55 -0500 (EST) (envelope-from gedau@isa.mim.com.au) Received: from mimhexch2.mim.com.au (unverified) by mimhimta.mim.com.au (Content Technologies SMTPRS 4.1.2) with ESMTP id for ; Mon, 28 Feb 2000 16:25:04 +1000 Received: from isaexch3.isa.mim.com.au (ISAEXCH3 [158.54.7.84]) by mimhexch2.mim.com.au with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id FRTRQ02R; Mon, 28 Feb 2000 16:23:59 +1000 Received: by ISAEXCH3 with Internet Mail Service (5.5.2448.0) id ; Mon, 28 Feb 2000 16:22:54 +1000 Message-ID: <69D6F577E4ADD311ABE600805FFE80580EBCF9@isaexch.isa.mim.com.au> From: George Dau To: "'pgsql-sql@postgresql.org'" Subject: prob with aggregate and group by - returns multiples Date: Mon, 28 Feb 2000 16:22:46 +1000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="ISO-8859-1" 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. **************************************************************