Hi everybody.I'm Facing a ploblem to calculate running average.I am
explaining the scenario.
In my table there are three column only
1.col1
2.col2
3.col3
In my query what to show another column with running average
of previous 5 Rows.Is it possible through query?

Example
-------
My table already contains

Col1 Col2 Col3
---- ---- -----
1 a 2
2 b 4
3 c 6
4 d 8
5 e 10
6 f 12
7 g 14
8 h 20
9 i 2


In my query output I want to show the
data like this format

Col1 Col2 Col3 Col4
---- ---- ------ ------
1 a 2 0
2 b 4 0
3 c 6 0
4 d 8 0
5 e 10 6---Average first 5 rows(1-5)
6 f 12 8---Average next 5 rows(2-6)
7 g 14 10--Average next 5 rows(3-7)
8 h 20 12--Average next 5 rows(4-8)
9 i 2 11--Average next 5 rows(5-9)

hope it will be clear to all of you.If
any problem in understanding
please let me inform.
Thanks
Arijit Chatterjee

"Arijit Chatterjee" <arijitchatterjee123 (AT) yahoo (DOT) co.in> wrote

Hi Arijit,

What you want to do is often called a moving average. Logically, it is a
quota query over a self-theta-join summarized to calculate the average.
Unfortunately, SQL has no short-hand for quota queries. There are multiple
ways to write a quota query in SQL--none of which are simple or obvious. As
a result, the query you want to write is messy in SQL.

Regards,
Bob

Tested on DB2 UDB.

WITH MyTable (Col1 , Col2 , Col3) AS (
VALUES
(1 , 'a' , 2)
,(2 , 'b' , 4)
,(3 , 'c' , 6)
,(4 , 'd' , 8)
,(5 , 'e' , 10)
,(6 , 'f' , 12)
,(7 , 'g' , 14)
,(8 , 'h' , 20)
,(9 , 'i' , 2)
)

SELECT Col1 , Col2 , Col3
, CASE
WHEN COUNT(*)
OVER(ORDER BY Col1, Col2
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >= 5 THEN
AVG(Col3)
OVER(ORDER BY Col1, Col2
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
ELSE 0
END Col4
FROM MyTable
;
---------------------------------------------------

COL1 COL2 COL3 COL4
----------- ---- ----------- -----------
1 a 2 0
2 b 4 0
3 c 6 0
4 d 8 0
5 e 10 6
6 f 12 8
7 g 14 10
8 h 20 12
9 i 2 11

9 record(s) selected.

arijitchatterjee123 (AT) yahoo (DOT) co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0307070325.26880e5c (AT) posting (DOT) google.com>...

[...]

Check if your db supports "window functions". If not, here is a couple of variants:


Can you assume that c1 is the "rownumber" of c3. In that case you can simply do:

select
t1.c1,
(select sum(c3) / count(1) from t
where c1 > (t1.c1-5) and c1 <= (t1.c1)
having count(1) = 5) as run_avg
from t t1

if not it gets a bit more complicated:

select t3.c1, sum(t4.c2) / count(1) as run_avg
from (
select t1.c1, t1.c2, count(1) from t t1, t t2
where t1.c1 >= t2.c1 group by t1.c1, t1.c2
) t3(c1,c2,rn),
(select t1.c1, t1.c2, count(1) from t t1, t t2
where t1.c1 >= t2.c1 group by t1.c1, t1.c2
) t4(c1, c2, rn) where
t4.rn > t3.rn-5 and
t4.rn <= t3.rn
group by t3.c1


HTH
/Lennart

"Arijit Chatterjee" <arijitchatterjee123 (AT) yahoo (DOT) co.in> wrote

This is a Standard SQL solution, tested with MS SQL Server 2000.

CREATE TABLE T
(
col1 INT NOT NULL PRIMARY KEY,
col2 CHAR(1) NOT NULL,
col3 INT NOT NULL
)

INSERT INTO T (col1, col2, col3)
VALUES (1, 'a', 2)
INSERT INTO T (col1, col2, col3)
VALUES (2, 'b', 4)
INSERT INTO T (col1, col2, col3)
VALUES (3, 'c', 6)
INSERT INTO T (col1, col2, col3)
VALUES (4, 'd', 8)
INSERT INTO T (col1, col2, col3)
VALUES (5, 'e', 10)
INSERT INTO T (col1, col2, col3)
VALUES (6, 'f', 12)
INSERT INTO T (col1, col2, col3)
VALUES (7, 'g', 14)
INSERT INTO T (col1, col2, col3)
VALUES (8, 'h', 20)
INSERT INTO T (col1, col2, col3)
VALUES (9, 'i', 2)

SELECT T1.col1, T1.col2, T1.col3,
CASE WHEN COUNT(*) = 5 THEN AVG(T2.col3)
ELSE 0
END AS col4
FROM T AS T1
INNER JOIN
T AS T2
ON T2.col1 BETWEEN T1.col1 - 4 AND T1.col1
GROUP BY T1.col1, T1.col2, T1.col3
ORDER BY T1.col1

col1 col2 col3 col4
1 a 2 0
2 b 4 0
3 c 6 0
4 d 8 0
5 e 10 6
6 f 12 8
7 g 14 10
8 h 20 12
9 i 2 11

Regards,
jag

arijitchatterjee123 (AT) yahoo (DOT) co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0307070325.26880e5c (AT) posting (DOT) google.com>...
Thanks everybody.
You gave me the answer.
Regards
Arijit Chatterjee