Received: from fep7.cogeco.net (smtp.cogeco.net [216.221.81.25]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0OIYdl29023 for ; Thu, 24 Jan 2002 13:34:39 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from xyzzy.lan.internal (d226-89-25.home.cgocable.net [24.226.89.25]) by fep7.cogeco.net (Postfix) with ESMTP id A35E25EB8; Thu, 24 Jan 2002 13:34:37 -0500 (EST) Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=drew) by xyzzy.lan.internal with esmtp (Exim 3.33 #1 (Debian)) id 16Toho-0000un-00; Thu, 24 Jan 2002 13:34:36 -0500 Subject: Re: auto group by From: "Andrew G. Hammond" To: Markus Bertheau Cc: postgres sql list In-Reply-To: <1011895246.29182.45.camel@entwicklung01.cenes.de> References: <1011895246.29182.45.camel@entwicklung01.cenes.de> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-CUKf2G1qbYAgTzlv0WdK" X-Mailer: Evolution/1.0 (Preview Release) Date: 24 Jan 2002 13:34:35 -0500 Message-Id: <1011897276.3198.2.camel@xyzzy> Mime-Version: 1.0 X-Archive-Number: 200201/276 X-Sequence-Number: 6360 --=-CUKf2G1qbYAgTzlv0WdK Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Thu, 2002-01-24 at 13:00, Markus Bertheau wrote: Hello again, =20=20=20=20 here's another question: =20=20=20=20 suppose there's a table personen: =20=20=20=20 personen_id int primary key, data text =20=20=20=20 and a table orders: =20=20=20=20 order_id int primary key, personen_id int, data text Might as well do it the "Postgres Way". CREATE TABLE personen ( personen_id SERIAL PRIMARY KEY, name TEXT NOT NULL=20 ); CREATE TABLE order ( order_id SERIAL PRIMARY KEY, personen_id INT REFERENCES personen=20 ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, data TEXT=20 ); . 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 =3D orders.personen_id gro= up by personen.personen_id, personen.text =20=20=20=20 Suppose you had some more data in personen, like 10 extra fields, and you want them to be included in the result. You would have to mention each of them in the group by clause, not only say the personen_id. But this information that is given to the db server seems a bit redundant to me. If the personen_id is the same (which qualifies these rows for one group), every single other field is the same also. Why isn't it automagically included in the group by clause? You cannot do anything useful with them but to group by them. =20=20=20=20 select [1] from tbl1 join tbl2 on tbl1.i=3Dtbl2.i =20=20=20=20 Like: every row from tbl1 that is mentioned as is (that is not used in an aggregate function) in the [1] marked location is automagically included in the group by clause. =20=20=20=20 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. :) --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org=20=20 http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --=-CUKf2G1qbYAgTzlv0WdK Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iQCVAwUAPFBTu8aBTSA3ULb1AQITSAP+IF4i4ARaQ2aKc7eD2csbth7j7Nke8yHn 06jXZr0TqnW5QyaH4Vnnef20Jz/PZPNjpqdotcEf6fnt0tdEh8w0MJtHXpZReJ41 sYC+qy7nJlf0CCxQkDOg+zbyRQFhTbZq2sAaq/A+KRNDMznuEoAo4BV9r96/vL0m R8HeLTqF4G0= =mV4w -----END PGP SIGNATURE----- --=-CUKf2G1qbYAgTzlv0WdK--