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
