Re: looking for some suggestions

Haven't tried this, but I think the aggregate sums in the sequence that the records come so if you want ordered by age then you would force your list to be ordered by birthdate before it is summed. This is a little tricky though since the order by date would screw up your group by so to get around that, you would do a subselect that orders your list and then sum that. Hopefully this will work.

So something like

SELECT childparent.parent, sum(childparent.child || ' ') AS "Children"
(SELECT p.fname As parent, c.fname As child
FROM persons p
LEFT JOIN dependents d ON p.person_id = d.parent_id
LEFT JOIN persons c ON d.child_id = c.person_id
ORDER BY p.fname, c.birth_date) as childparent
GROUP BY childparent.parent ;

________________________________

From: Chansup Byun [mailto:Chansup(dot)Byun(at)Sun(dot)COM]
Sent: Mon 5/8/2006 9:20 AM
To: Obe, Regina DND\MIS
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] looking for some suggestions

Obe, Regina DND\MIS wrote:
> I think the most efficient way to do this is with an aggregate function.
>
> You can define an aggregate for a varchar and text if one doesn't exist
> in your database - like so
>
> CREATE AGGREGATE sum(
> BASETYPE=text,
> SFUNC=textcat,
> STYPE=text
> );
> ALTER AGGREGATE sum(text) OWNER TO postgres;
>
> Then you can rewrite your sql statement like so
>
> SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
> FROM persons p LEFT JOIN dependents d ON p.person_id = d.parent_id LEFT
> JOIN c.persons c ON d.child_id = c.person_id
> GROUP BY p.fname
>

I followed your suggestion and was able to aggregate child names.

boc=# SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
boc-# FROM persons p
boc-# LEFT JOIN dependents d ON p.person_id = d.parent_id
boc-# LEFT JOIN persons c ON d.child_id = c.person_id
boc-# GROUP BY p.fname;
parent | Children
------------+--------------------
Momi Three |
Dadj Four |
Kidc One |
Dadh Three |
Mome Two | Kidf Two Kidg Two
Momb One | Kidc One
Dadd Two | Kidf Two Kidg Two
Dada One | Kidc One
Kidg Two |
Kidf Two |

Now my next question is how to make sure kids are ordered by ages?

Suppose that I extend the persons table and add birth_date filed, how
can I order child names by age? Is there a way to put such a check in to
the aggregate sum(text) function?

create table persons
(
person_id serial ,
fname varchar(32) ,
birth_date date ,
CONSTRAINT person_pk PRIMARY KEY(person_id)
);

Thanks,

- Chansup

>
>
>
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Chansup Byun
> Sent: Friday, May 05, 2006 3:22 PM
> To: pgsql-novice(at)postgresql(dot)org
> Cc: Chansup(dot)Byun(at)Sun(dot)COM
> Subject: [NOVICE] looking for some suggestions
>
>
> Hi,
>
> I am looking for some suggestions on my test example.
>
> I have two tables: one for persons and the other for dependents table,
> which is shown below. I would like to concatenate all children of the
> same parents in a single string such as:
>
> Children
> -----------------
> Kidc One
> Kidf Two, Kidg Two
>
> Can anyone help me how to do that from the following tables?
>
> I have installed PostgreSQL 8.1.3.
>
> The tables and their records are given below.
>
> create table persons
> (
> person_id serial ,
> fname varchar(32) ,
> CONSTRAINT person_pk PRIMARY KEY(person_id)
> );
>
> create table dependents
> (
> parent_id integer not null,
> child_id integer not null,
> CONSTRAINT dependents_pk PRIMARY KEY(parent_id, child_id)
> );
>
>
> insert into persons(fname) values('Dada One');
> insert into persons(fname) values('Momb One');
> insert into persons(fname) values('Kidc One');
>
> insert into persons(fname) values('Dadd Two');
> insert into persons(fname) values('Mome Two');
> insert into persons(fname) values('Kidf Two');
> insert into persons(fname) values('Kidg Two');
>
> insert into persons(fname) values('Dadh Three');
> insert into persons(fname) values('Momi Three');
>
> insert into persons(fname) values('Dadj Four');
>
> insert into dependents(parent_id, child_id) values('1', '3'); insert
> into dependents(parent_id, child_id) values('2', '3'); insert into
> dependents(parent_id, child_id) values('4', '6'); insert into
> dependents(parent_id, child_id) values('5', '6'); insert into
> dependents(parent_id, child_id) values('4', '7'); insert into
> dependents(parent_id, child_id) values('5', '7');
>
> The following attempt can list all the children but I'm not sure how to
> group them into a single string based on their parents.
>
> SELECT DISTINCT c.fname AS "Children"
> FROM persons p, persons c, dependents d
> WHERE d.parent_id = p.person_id AND
> d.child_id = c.person_id
> ;
>
> Children
> ----------
> Kidc One
> Kidf Two
> Kidg Two
>
>
> Thanks,
>
> - Chansup
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally
> privileged and/or exempt from disclosure pursuant to Massachusetts
> law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and
> delete the material from any computer.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend