Re: Group By?

Yes I am trying to insert all valves into the same row as their associated
mon.

Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby(at)pervasive(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)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(at)masergy(dot)com>
>> To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(at)wolff(dot)to>
>> >To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
>> >Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "Postgre General"
>> ><pgsql-general(at)postgresql(dot)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(at)shaw(dot)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(at)masergy(dot)com>
>> >>>To: "Postgre General" <pgsql-general(at)postgresql(dot)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(at)pervasive(dot)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