From: Raj A <raj.ayappan@gmail.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Andrew Kroeger <andrew@sprocks.gotdns.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: aggregate query
Date: Tue, 29 May 2007 22:32:46 +1000
Message-ID: <ddcb1c340705290532x11edb333sd5e9318e17ee951f@mail.gmail.com> (raw)
In-Reply-To: <87odk3j43u.fsf@oxford.xeocode.com>
References: <ddcb1c340705282132p656de07cy5618de275fc1f1f7@mail.gmail.com>
<465BDE9F.4010605@sprocks.gotdns.com>
<87odk3j43u.fsf@oxford.xeocode.com>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: raj.ayappan@gmail.com, stark@enterprisedb.com, andrew@sprocks.gotdns.com
Subject: Re: aggregate query
In-Reply-To: <ddcb1c340705290532x11edb333sd5e9318e17ee951f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox