Mailbox-Line: From mallah@trade-india.com  Thu Jul  4 11:11:10 2002
Content-Type: text/plain;
  charset="iso-8859-1"
From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: teknokrat@yahoo.com (teknokrat), pgsql-sql@postgresql.org
Subject: Re: Can this be done with sql?
Date: Thu, 4 Jul 2002 20:20:29 +0530
User-Agent: KMail/1.4.1
References: <1cbae208.0207031032.7d1be1a2@posting.google.com>
In-Reply-To: <1cbae208.0207031032.7d1be1a2@posting.google.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207042020.29657.mallah@trade-india.com>

Hi ,

you can use GROUP BY , at the expense of adding one more column of SERIAL d=
ata type,

say,

select * from t_a  limit 10;
access_log=3D# SELECT * from t_a   limit 15;

 sno | value
-----+-------
   1 |  4533
   2 |  2740
   3 |  9970

   4 |  6445
   5 |  2220
   6 |  2301

   7 |  6847
   8 |  5739
   9 |  5286

  10 |  5556
  11 |  9309
  12 |  9552

  13 |  8589
  14 |  5935
  15 |  2382
(15 rows)

if you want avg for every third item you can use:

access_log=3D# SELECT avg(value) from t_a group by (1+(sno-1)/3)  limit 5;=
=20=20

      avg
-----------------
 5747.6666666667
 3655.3333333333
 5957.3333333333
 8139.0000000000
 5635.3333333333
(5 rows)

you can replace 3 in the SQL with any number for grouping that many records.
if you need  MEAN , STDDEV , MAX, MIN  etc you can use approprite AGGREGATE=
 that PGSQL supports
for numbers eg for MAX=20

access_log=3D# SELECT MAX(value) from t_a group by (1+(sno-1)/3)  limit 5;
 max
------
 9970
 6445
 6847
 9552
 8589
(5 rows)

Regds
MAlz.


On Thursday 04 July 2002 00:02, teknokrat wrote:
> In my database i have values recorded in one minute intervals. I would
> like a query that can get me results for other time intervals. For
> example - return maximum value in each 3 minute interval. Any ideas
> how i can do this with sql? I tried writing a procedure in plsql but i
> am told it does not support tuples as output. I can get the all the
> one minute intervals and process them to get me three minute intervals
> in my application but i would rather not do the expensive call for the
> one minute intervals in the first place due to the large number of
> data. any ideas?
>
> thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--=20
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.