Message-ID: <434BCB1F.7040403@archonet.com>
Date: Tue, 11 Oct 2005 15:24:31 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.4.1 (X11/20051006)
MIME-Version: 1.0
To: Neil Saunders <n.j.saunders@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Difference from average
References: <ddcd549e0510110620v601d5d5es6c88e87e609d1a73@mail.gmail.com>
In-Reply-To: <ddcd549e0510110620v601d5d5es6c88e87e609d1a73@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Neil Saunders wrote:
> Hi all,
> 
> I'm developing a property rental database. One of the tables tracks
> the price per week for different properties:
> 
> CREATE TABLE "public"."prices" (
>   "id" SERIAL,
>   "property_id" INTEGER,
>   "start_date" TIMESTAMP WITHOUT TIME ZONE,
>   "end_date" TIMESTAMP WITHOUT TIME ZONE,
>   "price" DOUBLE PRECISION NOT NULL
> ) WITH OIDS;
> 
> CREATE INDEX "prices_idx" ON "public"."prices"
>   USING btree ("property_id");
> 
> I'd like to display the prices per property in a table, with each row
> coloured different shades; darker shades representing the more
> expensive periods for that property. To do this, I propose to
> calculate the percentage difference of each rows price from the
> average for that property, so if for example I have two rows, one for
> price=200 and one for price=300, i'd like to retrieve both records
> along with the calculated field indicating that the rows are -20%,
> +20% from the average, respectively.
> 
> I've started with the following query, but since I'm still learning
> how PostgreSQL works, I'm confused as to the efficiency of the
> following statement:
> 
> SELECT *, (price - (SELECT avg(price) from prices)) as diff FROM prices;

I'd personally write it something like:

SELECT
   prices.property_id,
   prices.price AS actual_price,
   averages.avg_price,
   (averages.avg_price - prices.price) AS price_diff
   ((averages.avg_price - prices.price)/averages.avg_price) AS pc_diff
FROM
   prices,
   (SELECT property_id, avg(price) as avg_price FROM prices) AS averages
WHERE
   prices.property_id = averages.property_id
;

That's as much to do with how I think about the problem as to any 
testing though.

-- 
   Richard Huxton
   Archonet Ltd