pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

using Avg()
2+ messages / 2 participants
[nested] [flat]

* using Avg()
@ 2003-02-27 16:59 Mark Connelly@South Central <Mark.Connelly@ntl.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Mark Connelly@South Central @ 2003-02-27 16:59 UTC (permalink / raw)
  To: pgsql-sql

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
 
TIA
 
Mark Connelly
 
 
 
 
 


The contents of this email and any attachments are sent for the personal attention
of the addressee(s) only and may be confidential.  If you are not the intended
addressee, any use, disclosure or copying of this email and any attachments is
unauthorised - please notify the sender by return and delete the message.  Any
representations or commitments expressed in this email are subject to contract.
  
ntl Group Limited




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: using Avg()
@ 2003-03-04 11:14 Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Christoph Haller @ 2003-03-04 11:14 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Mark.Connelly@ntl.com

>
> 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-04 11:14 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 16:59 using Avg() Mark Connelly@South Central <Mark.Connelly@ntl.com>
2003-03-04 11:14 Re: using Avg() Christoph Haller <ch@rodos.fzk.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox