Re: aggregate query

"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
whenbay=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