Re: 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