Re: Group By? - Mailing list pgsql-general

Try this (untested):

INSERT INTO auto_control( monitor, valve )
    SELECT m.device_id, v.device_id
        FROM control m
            JOIN control v ON (m.association = v.association)
;

On Mon, Nov 28, 2005 at 03:41:53PM -0800, Bob Pawley wrote:
> Yes I am trying to insert all valves into the same row as their associated
> mon.
>
> Bob
> ----- Original Message -----
> From: "Jim C. Nasby" <jnasby@pervasive.com>
> To: "Bob Pawley" <rjpawley@shaw.ca>
> Cc: "Guy Rouillier" <guyr@masergy.com>; "PostgreSQL General"
> <pgsql-general@postgresql.org>
> Sent: Monday, November 28, 2005 2:18 PM
> Subject: Re: [GENERAL] Group By?
>
>
> >So are you trying to get a list of all 'mon's and 'valve's for each
> >given association?
> >
> >On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
> >>Guy
> >>
> >>
> >>
> >>Sorry about the chart. It held together when I sent it to myself.
> >>
> >>
> >>
> >>I'll try to make it clear in the way you suggest, by a truncated example.
> >>
> >>
> >>
> >>Create table control (device_id serial, type varchar, association int4)
> >>
> >>Create table auto_control (loop_id serial, monitor int4, valve int4)
> >>
> >>
> >>
> >>Insert into control (type, association) Note - mon and valve are types of
> >>device that together make a loop. A loop can be from 1 to 7 devices..
> >>
> >>Values ('mon', '1') - serial 1
> >>
> >>Values ('valve', '2') - serial 2
> >>
> >>Values ('mon', '2') - serial 3
> >>
> >>Values ('valve', '1') - serial 4
> >>
> >>Values ('valve', '2') - serial 5
> >>
> >>
> >>
> >>I want to transfer the serial device_id number for mon '1' into the same
> >>row as valve '1' in the tables auto_control. Similarily
> >>
> >>
> >>
> >>Those two rows would look like this.
> >>
> >>Table (loop_id serial, monitor int4, valve int4)
> >>
> >>Row 1 ( 1, 1, 4, )
> >>
> >>Row 2 (2, 2, 3, 5)
> >>
> >>
> >>
> >>Once this is done the devices will be organized into loops and each
> >>device
> >>in the loop will have a direct link to other parts of the database.
> >>
> >>
> >>
> >>I would like to know if this is possible with SQL, or would it be more
> >>suited to the host language?
> >>
> >>
> >>
> >>Would it be possible in SQL to have the information transferred into the
> >>auto_control table as the information is being entered or would the
> >>control
> >>table need to be fully completed?
> >>
> >>
> >>
> >>Hope this is finally clear.
> >>
> >>
> >>
> >>Thanks for you help.
> >>
> >>
> >>
> >>Bob
> >>
> >>----- Original Message -----
> >>From: "Guy Rouillier" <guyr@masergy.com>
> >>To: "PostgreSQL General" <pgsql-general@postgresql.org>
> >>Sent: Wednesday, November 23, 2005 2:17 PM
> >>Subject: Re: [GENERAL] Group By?
> >>
> >>
> >>Bob Pawley wrote:
> >>>Bruno
> >>>
> >>>The table I previously sent came through distorted and probabley
> >>>caused misunderstanding.
> >>>
> >>>The table control and auto_control are both permanent table. I want to
> >>>reshuffle how the information is associated from one table to another
> >>>with the link between table by way of the device_id.
> >>>
> >>>Following is the example which I stabilized and tested for e-mail.
> >>
> >>Sorry, Bob, I think the reason you haven't gotten a response is that the
> >>information you are trying to convey below is very unclear.  Perhaps you
> >>can just provide a create table statement and a bunch of insert
> >>statements?  Then summarize again what you are trying to accomplish.
> >>
> >>>
> >>>Bob
> >>>
> >>>      Control
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>      device_id
> >>>     type
> >>>     association
> >>>
> >>>
> >>>      serial
> >>>     varchar
> >>>     int4
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>      1
> >>>     mon
> >>>     1
> >>>
> >>>
> >>>      2
> >>>     valve
> >>>     2
> >>>
> >>>
> >>>      3
> >>>     valve
> >>>     1
> >>>
> >>>
> >>>      4
> >>>     mon
> >>>     2
> >>>
> >>>
> >>>      5
> >>>     valve
> >>>     1
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>      Auto_control
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>      loop_id
> >>>     mon
> >>>     valve_a
> >>>     valve_b
> >>>
> >>>      serial
> >>>     int4
> >>>     int4
> >>>     int4
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>      1
> >>>     1
> >>>     3
> >>>     5
> >>>
> >>>      2
> >>>     2
> >>>     4
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>----- Original Message -----
> >>>From: "Bruno Wolff III" <bruno@wolff.to>
> >>>To: "Bob Pawley" <rjpawley@shaw.ca>
> >>>Cc: "Guy Rouillier" <guyr@masergy.com>; "Postgre General"
> >>><pgsql-general@postgresql.org>
> >>>Sent: Monday, November 21, 2005 10:07 PM
> >>>Subject: Re: Group By?
> >>>
> >>>
> >>>>On Mon, Nov 21, 2005 at 21:53:10 -0800,
> >>>> Bob Pawley <rjpawley@shaw.ca> wrote:
> >>>>>Here's what I want to do.
> >>>>>
> >>>>>Table control contains values (mon and valves) that are associated
> >>>>>by numbers inserted into the associated column.
> >>>>>
> >>>>>I want to transfer the serial _id number of the items associated by
> >>>>>the value '1' into the appropriate columns of the first row of the
> >>>>>table auto_control. All items associated with the value '2' into the
> >>>>>second row - etc. etc.
> >>>>
> >>>>You don't really want to do that. Tables have fixed numbers of
> >>>>columns and what you want to do doesn't result in a fixed number of
> >>>>columns.
> >>>>
> >>>>If you want to generate a report with that format, then I think there
> >>>>is a contrib module (crosstabs?) that will do this kind of thing. You
> >>>>could also have a report app do it for you. In the report app method,
> >>>>you would be best to return rows ordered by association and then
> >>>>device_ID and have the app check for when the association value
> >>>>changes.
> >>>>
> >>>>>
> >>>>>Is this best accomplished by a 'group by' command or subset???
> >>>>>
> >>>>>Bob
> >>>>>     Control
> >>>>>
> >>>>>     device_ID type association
> >>>>>     serial varchar int4
> >>>>>
> >>>>>     1 mon 1
> >>>>>     2 valve 2
> >>>>>     3 valve 1
> >>>>>     4 mon 2
> >>>>>     5 valve 1
> >>>>>
> >>>>>
> >>>>>     Auto_control
> >>>>>
> >>>>>     loop_id mon valve valve
> >>>>>     serial int4 int4 int4
> >>>>>     1 1 3 5
> >>>>>     2 2 4
> >>>>>
> >>>>>
> >>>>>
> >>>>>----- Original Message -----
> >>>>>From: "Guy Rouillier" <guyr@masergy.com>
> >>>>>To: "Postgre General" <pgsql-general@postgresql.org>
> >>>>>Sent: Monday, November 21, 2005 4:25 PM
> >>>>>Subject: Re: [GENERAL] Group By?
> >>>>>
> >>>>>
> >>>>>Converted your message to plain text as preferred on most mailing
> >>>>>lists.
> >>>>>
> >>>>>Bob Pawley wrote:
> >>>>>>I want to take the serial ID of several values in different rows in
> >>>>>>one table and insert them into a single row of another table.
> >>>>>>
> >>>>>>Would the 'group by' command be the best way to do this?
> >>>>>
> >>>>>Could you provide an actual example?  The wording of your question
> >>>>>is a little vague and an example might help solicit an answer to the
> >>>>>actual problem.  For example, are these serial ID values all in a
> >>>>>the same column in the source table?  Or is each one in a different
> >>>>>column?  And what is the selection criteria that brings these
> >>>>>results together?
> >>>>>
> >>>>>--
> >>>>>Guy Rouillier
> >>>>>
> >>>>>---------------------------(end of
> >>>>>broadcast)---------------------------
> >>>>>TIP 3: Have you checked our extensive FAQ?
> >>>>>
> >>>>>              http://www.postgresql.org/docs/faq
> >>>>>
> >>>>>
> >>>>>---------------------------(end of
> >>>>>broadcast)---------------------------
> >>>>>TIP 4: Have you searched our list archives?
> >>>>>
> >>>>>              http://archives.postgresql.org
> >>
> >>
> >>
> >>--
> >>Guy Rouillier
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Have you searched our list archives?
> >>
> >>              http://archives.postgresql.org
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 5: don't forget to increase your free space map settings
> >>
> >
> >--
> >Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> >Pervasive Software      http://pervasive.com    work: 512-231-6117
> >vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: Have you checked our extensive FAQ?
> >
> >              http://www.postgresql.org/docs/faq
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461