Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBJJdu611478 for ; Tue, 19 Dec 2000 14:39:56 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from vmx.mediaparks.lv (vmx.mediaparks.lv [62.85.15.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJG9l694592 for ; Tue, 19 Dec 2000 11:09:47 -0500 (EST) (envelope-from emils@grafton.lv) Received: (from mail@localhost) by vmx.mediaparks.lv (8.9.3/8.9.3) id SAA51216 for pgsql-sql@postgresql.org; Tue, 19 Dec 2000 18:09:44 +0200 (EET) (envelope-from emils@grafton.lv) X-Authentication-Warning: vmx.mediaparks.lv: mail set sender to emils@grafton.lv using -f Received: from 159.148.192.252 by 62.85.15.11 id EZ+8GyUx ; 19 Dec 2000 18:09:39 EET Received: from mortyr (mortyr.grafton.lv [159.148.192.196]) by home.grafton.lv (8.9.3/8.8.7) with ESMTP id SAA13651 for ; Tue, 19 Dec 2000 18:09:20 -0200 From: "Emils Klotins" To: pgsql-sql@postgresql.org Date: Tue, 19 Dec 2000 18:10:39 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: group by: properly? Message-ID: <3A3FA49F.26165.55FCFF8@localhost> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200012/224 X-Sequence-Number: 560 Hello, I must confess I have always been a bit baffled by the GROUP BY, therefore I would appreciate if you could tell me if there's a better way: I have the table "items": id | integer | not null default nextval('items_id_seq'::text) seller_id | integer | not null ==== material | integer | not null item_kind | integer | finishing | integer | ==== amount | integer | not null What I need is to SELECT the list of items that have the same values in fields in between the ==== marks. The material, finishing and item_kind are references to the appropriate id fields in tables material, finishing and item_kinds: materials: id SERIAL, name_en text and I would like to get the name of the material rather then the id from the query. So I wrote: SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en AS finishing, amount FROM items I, item_kinds P, materials M, finish F WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind This gives me the list of items with the names rather than id's already prepared, the list could be something like: material itemtype finishing amount 'birch' 'SCAFFOLD BOARDS' 'levelled' 5 'birch' 'SCAFFOLD BOARDS' 'levelled' 33 'birch' 'SCAFFOLD BOARDS' 'levelled' 4 'oak' 'SCAFFOLD BOARDS' '' 7 'oak' 'C.L.S.' '' 66 Now, I want this query to give me only the one value for the items that differ only with the amount. so that i have: 'birch' 'SCAFFOLD BOARDS' 'levelled' 42 'oak' 'SCAFFOLD BOARDS' '' 7 'oak' 'C.L.S.' '' 66 The following GROUP BY accomplishes it: SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en AS finishing, sum(amount) FROM items I, item_kinds P, materials M, finish F WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind GROUP BY M.name_en,P.name_en,F.name_en; The question is: assuming I will have quite a lot of rows in the result, will the performance be OK to GROUP BY the resulting text fields rather then by I.item_kind, I.material indexed numeric fields? Is it possible to rewrite the query so that GROUP BY (or any alternative construct) deals with the numeric fields and yet I can receive the textual output (that I need) via a single query? Any comments appreciated, Emils