Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0OI0ll14198 for ; Thu, 24 Jan 2002 13:00:47 -0500 (EST) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (p3E9EC076.dip.t-dialin.net [62.158.192.118]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id g0OI1lD21304 for ; Thu, 24 Jan 2002 19:01:47 +0100 Subject: auto group by From: Markus Bertheau To: postgres sql list Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-rx9xtkufnAEZQLD/X89Z" X-Mailer: Evolution/1.0.1 Date: 24 Jan 2002 19:00:45 +0100 Message-Id: <1011895246.29182.45.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200201/272 X-Sequence-Number: 6356 --=-rx9xtkufnAEZQLD/X89Z Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hello again, here's another question: suppose there's a table personen: personen_id int primary key, data text and a table orders: order_id int primary key, personen_id int, 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 =3D orders.personen_id group by personen.personen_id, personen.text 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. select [1] from tbl1 join tbl2 on tbl1.i=3Dtbl2.i 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. Why? Enlightenment appreciated. Markus Bertheau --=-rx9xtkufnAEZQLD/X89Z Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA8UEvNKNEjAb345r4RAmssAJ9U3h+f0sWXG3xZ/h+4clMxL1LtYgCfZQ06 Dv3j8E17MJrePQE9OXnUkwY= =+RWr -----END PGP SIGNATURE----- --=-rx9xtkufnAEZQLD/X89Z--