Re: auto group by - Mailing list pgsql-sql

From Andrew G. Hammond
Subject Re: auto group by
Date
Msg-id 1011897276.3198.2.camel@xyzzy
Whole thread Raw
In response to auto group by  (Markus Bertheau <twanger@bluetwanger.de>)
List pgsql-sql
On Thu, 2002-01-24 at 13:00, Markus Bertheau wrote:   Hello again,      here's another question:      suppose there's a
tablepersonen:      personen_id int primary key,   data text      and a table orders:      order_id int primary key,
personen_idint,   data text 

Might as well do it the "Postgres Way".

CREATE TABLE personen (            personen_id SERIAL PRIMARY KEY,            name TEXT NOT NULL
);

CREATE TABLE order (           order_id SERIAL PRIMARY KEY,           personen_id INT REFERENCES personen
     ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,           data TEXT  
);
   . Now I want to know how many orders each person has, who has at least   one order. I would use the following SQL
query:  select personen.personen_id, personen.text, count(order_id) from   personen join orders on personen.personen_id
=orders.personen_id group   by personen.personen_id, personen.text      Suppose you had some more data in personen,
like10 extra fields, and   you want them to be included in the result. You would have to mention   each of them in the
groupby clause, not only say the personen_id. But   this information that is given to the db server seems a bit
redundantto   me. If the personen_id is the same (which qualifies these rows for one   group), every single other field
isthe same also. Why isn't it   automagically included in the group by clause? You cannot do anything   useful with
thembut to group by them.      select [1] from tbl1 join tbl2 on tbl1.i=tbl2.i      Like: every row from tbl1 that is
mentionedas is (that is not used in   an aggregate function) in the [1] marked location is automagically   included in
thegroup by clause.      Why? 

First off, I think this query will acheive what you're looking for

SELECT * FROM personen
NATURAL JOIN
SELECT personen_id, count(order_id) AS orders FROM order GROUP BY
personen_id

Assuming of course there's no "orders" column in personen.
   Enlightenment appreciated.

I believe that there was a posting on this very topic a week or two
ago.  From what I recall, it's allowed under the latest SQL (98?), but
not by any of the previous flavours.  Try browsing back through the
archives. :)

--
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

pgsql-sql by date:

Previous
From: Marius Andreiana
Date:
Subject: Keeping information changes history
Next
From: Tom Lane
Date:
Subject: Re: Keeping information changes history