Re: looking for some suggestions - Mailing list pgsql-novice

From Obe, Regina DND\\MIS
Subject Re: looking for some suggestions
Date
Msg-id 53F9CF533E1AA14EA1F8C5C08ABC08D20CB923@ZDND.DND.boston.cob
Whole thread Raw
In response to Re: looking for some suggestions  ("Obe, Regina DND\\MIS" <robe.dnd@cityofboston.gov>)
Responses Re: looking for some suggestions
List pgsql-novice
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.Byun@Sun.COM]
Sent: Mon 5/8/2006 9:20 AM
To: Obe, Regina DND\MIS
Cc: pgsql-novice@postgresql.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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Chansup Byun
> Sent: Friday, May 05, 2006 3:22 PM
> To: pgsql-novice@postgresql.org
> Cc: Chansup.Byun@Sun.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

pgsql-novice by date:

Previous
From: Arnaud Lesauvage
Date:
Subject: Re: Trigger function to change data to correct datatype
Next
From: Chansup Byun
Date:
Subject: Re: looking for some suggestions