pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



Message-ID: <3A3FA49F.26165.55FCFF8@localhost>
Permalink:  ../3A3FA49F.26165.55FCFF8@localhost/
Also on:    postgresql.org/message-id/3A3FA49F.26165.55FCFF8@localhost

 · 

reply

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