From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Andrew Kroeger" <andrew(at)sprocks(dot)gotdns(dot)com> |
Cc: | "Raj A" <raj(dot)ayappan(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: aggregate query |
Date: | 2007-05-29 10:50:29 |
Message-ID: | 87odk3j43u.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Andrew Kroeger" <andrew(at)sprocks(dot)gotdns(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Raj A | 2007-05-29 12:32:46 | Re: aggregate query |
Previous Message | Andrew Kroeger | 2007-05-29 08:04:47 | Re: aggregate query |