From: Emils Klotins <emils@home.grafton.lv>
To: pgsql-sql@postgresql.org
Subject: group by: properly?
Date: Tue, 19 Dec 2000 18:10:39 +0200
Message-ID: <3A3FA49F.26165.55FCFF8@localhost> (raw)
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
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: emils@home.grafton.lv
Subject: Re: group by: properly?
In-Reply-To: <3A3FA49F.26165.55FCFF8@localhost>
References: <3A3FA49F.26165.55FCFF8@localhost>
* 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