Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 980949FB6B6
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 29 May 2007 07:50:39 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 91001-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 29 May 2007 07:50:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from oxford.xeocode.com (unknown [62.232.55.118])
	by postgresql.org (Postfix) with ESMTP id 22DD49FB295
	for <pgsql-sql@postgresql.org>; Tue, 29 May 2007 07:50:36 -0300 (ADT)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.67)
	(envelope-from <stark@enterprisedb.com>)
	id 1HszHZ-0002th-GR; Tue, 29 May 2007 11:50:29 +0100
From: Gregory Stark <stark@enterprisedb.com>
To: "Andrew Kroeger" <andrew@sprocks.gotdns.com>
Cc: "Raj A" <raj.ayappan@gmail.com>,  <pgsql-sql@postgresql.org>
Subject: Re: aggregate query
In-Reply-To: <465BDE9F.4010605@sprocks.gotdns.com> (Andrew Kroeger's message
	of "Tue, 29 May 2007 03:04:47 -0500")
Organization: EnterpriseDB
References: <ddcb1c340705282132p656de07cy5618de275fc1f1f7@mail.gmail.com>
	<465BDE9F.4010605@sprocks.gotdns.com>
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.sql" 232)
Date: Tue, 29 May 2007 11:50:29 +0100
Message-ID: <87odk3j43u.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/181
X-Sequence-Number: 28461

"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