pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Markus Bertheau <twanger@bluetwanger.de>
Cc: postgres sql list <pgsql-sql@postgresql.org>
Subject: Re: auto group by
Date: 24 Jan 2002 13:34:35 -0500
Message-ID: <1011897276.3198.2.camel@xyzzy> (raw)
In-Reply-To: <1011895246.29182.45.camel@entwicklung01.cenes.de>
References: <1011895246.29182.45.camel@entwicklung01.cenes.de>

On Thu, 2002-01-24 at 13:00, Markus Bertheau wrote:
    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

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, 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?

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

view thread (2+ messages)

Message-ID: <1011897276.3198.2.camel@xyzzy>
Permalink:  ../1011897276.3198.2.camel@xyzzy/
Also on:    postgresql.org/message-id/1011897276.3198.2.camel@xyzzy

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, twanger@bluetwanger.de
  Subject: Re: auto group by
  In-Reply-To: <1011897276.3198.2.camel@xyzzy>

* 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