Re: Group By? - Mailing list pgsql-general

Thank you I'll give it a try.

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 4:13 PM
Subject: Re: [GENERAL] Group By?


> 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