Message-ID: <3E648A8A.91C289CB@rodos.fzk.de>
Date: Tue, 04 Mar 2003 12:14:18 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Mark.Connelly@ntl.com
Subject: Re: using Avg()
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> I have a table with a column named SwErr   (Switch Error) with int
values
> date with datetime values and SwID with char(3)
> I am trying to get a subset back where the folowing is true
>
> select the avg(SwErr) for the last 30 days where SwId = 'XX1'
> Select the value of todays value of SwErr where SwId = 'XX1'
> if todays value of SwErr is greater than 4 times the SwErr Average
return in
> the value
>
Looks like you could use a plpgsql function, something like (untested)

CREATE OR REPLACE FUNCTION
get_dated_SwErr(CHAR) RETURNS INTEGER AS '
DECLARE
 thisSwID       ALIAS FOR $1;
 todaysSwErr    INT;
 avgSwErr       INT;
 avg4SwErr      INT;
 dateLimit      TIMESTAMP;
BEGIN

SELECT INTO dateLimit current_date - ''30 days''::interval ;

SELECT INTO todaysSwErr SwErr FROM <SwTable>
 WHERE SwID = thisSwID AND SwDate = current_date ;

SELECT INTO avgSwErr AVG(SwErr)::int FROM <SwTable>
 WHERE SwID = thisSwID AND SwDate BETWEEN current_date AND dateLimit ;

avg4SwErr := avgSwErr * 4 ;

IF todaysSwErr > avg4SwErr THEN
 RETURN todaysSwErr;
ELSE
 RETURN avgSwErr;
END IF;

END;
' LANGUAGE 'plpgsql' ;

then
SELECT SwID, get_dated_SwErr(SwID) FROM <SwTable>
 WHERE SwID = 'XX1' AND SwDate = current_date ;
should bring up the result.

Regards, Christoph