From: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Subject: auto group by
Date: 24 Jan 2002 19:00:45 +0100
Message-ID: <1011895246.29182.45.camel@entwicklung01.cenes.de> (raw)
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 = 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=tbl2.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
Attachments:
signature.asc (application/pgp-signature, 231B) download
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: twanger@bluetwanger.de
Subject: Re: auto group by
In-Reply-To: <1011895246.29182.45.camel@entwicklung01.cenes.de>
* 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