Re: aggregate query

Thank you guys!
I'm currently migrating an Oracle database to postgres and have
created tables using the scripts that were readily available.  Glad I
can now improve this old system.

On 29/05/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Andrew Kroeger" <andrew@sprocks.gotdns.com> writes:
>
> > Raj A wrote:
> >> I have a table
> >>
> >> CREATE TABLE survey_load
> >> (
> >>  meter_id character(5) NOT NULL,
> >>  number_of_bays integer NOT NULL,
> >>  bay_1_use integer,
> >>  bay_2_use integer,
> >>  bay_3_use integer,
> >>  bay_4_use integer,
> >>  bay_5_use integer,
> >>  date date NOT NULL,
> >>  inspection_id integer NOT NULL DEFAULT,
> >> )
> >>
> >> How do i present an aggregate query
> >>
> >> inspection_id  |  meter_id  |  bay_use
> >> 1                 12345        (value of bay_1_use)
> >> 1                 12345        (value of bay_2_use)
> >> 1                 12345        (value of bay_3_use)
> >> 2                 23456        (value of bay_1_use)
> >> 2                 23456        (value of bay_2_use)
> >> 2                 23456        (value of bay_3_use)
> >> 2                 23456        (value of bay_4_use)
> >> 2                 23456        (value of bay_5_use)
>
>
> >
> > If I understand your issue correctly, it seems like the denormalized
> > nature of your table is causing you some problems.
>
> True. Normalizing the tables would make this query easier which is a good sign
> that that's probably the right direction.
>
> If for some reason you can't or won't change the table definition there are a
> number of possible tricky answers given the current definition. Something like
> this for example:
>
> SELECT inspection_id, meter_id,
>        case when bay=1 then bay_1_use
>             when bay=2 then bay_2_use
>             when bay=3 then bay_3_use
>             when bay=4 then bay_4_use
>             when bay=5 then bay_5_use
>             else null
>         end AS bay_use
>   FROM (
>         SELECT *, generate_series(1,number_of_bays) AS bay
>           FROM survey_load
>        ) as x
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>
>