moving averag, multyple items

[nobloz]

Hi all,

I am fighting with this a while allready,
a moving average with mutilty items

The (example) tabel :

item datum volume
item1 1/1/2009 10
item1 1/2/2009 11
item1 1/3/2009 12
item1 1/4/2009 11
item1 1/5/2009 10
item1 1/6/2009 10
item1 1/7/2009 10

When i do this with only 1 item in the table it works pretty fine.

SELECT 
item,datum,volume, 
(SELECT SUM(t2.volume) / COUNT(t2.volume) 
FROM tradecount t2 
WHERE DATEDIFF(t1.datum, t2.datum) BETWEEN 0 AND 2) AS 'avg3day' 
FROM tradecount t1 
ORDER BY t1.item,t1.datum,

item datum volume avg3day
item1 1/1/2009 10 10.0000
item1 1/2/2009 11 10.5000
item1 1/3/2009 12 11.0000
item1 1/4/2009 11 11.3333
item1 1/5/2009 10 11.0000
item1 1/6/2009 10 10.3333
item1 1/7/2009 10 10.0000

The problem starts when i do the same with multiple items.

item datum volume
item1 1/1/2009 10
item1 1/2/2009 11
item1 1/3/2009 12
item1 1/4/2009 11
item1 1/5/2009 10
item1 1/6/2009 10
item1 1/7/2009 10
item2 1/1/2009 10
item2 1/2/2009 10
item2 1/3/2009 10
item2 1/4/2009 10
item2 1/5/2009 10
item2 1/6/2009 10
item2 1/7/2009 10
item3 1/1/2009 10
item3 1/2/2009 10
item3 1/3/2009 10
item3 1/4/2009 10
item3 1/5/2009 10
item3 1/6/2009 10
item3 1/7/2009 10

The value of the average change and all the items have the same moving value on the dates.
It seems that the query dont see that it has to do this for all invidual items, result is:

item datum volume avg3day
item1 1/1/2009 10 10.0000
item1 1/2/2009 11 10.1667
item1 1/3/2009 12 10.3333
item1 1/4/2009 11 10.4444
item1 1/5/2009 10 10.3333
item1 1/6/2009 10 10.1111
item1 1/7/2009 10 10.0000
item2 1/1/2009 10 10.0000
item2 1/2/2009 10 10.1667
item2 1/3/2009 10 10.3333
item2 1/4/2009 10 10.4444
item2 1/5/2009 10 10.3333
item2 1/6/2009 10 10.1111
item2 1/7/2009 10 10.0000
item3 1/1/2009 10 10.0000
item3 1/2/2009 10 10.1667
item3 1/3/2009 10 10.3333
item3 1/4/2009 10 10.4444
item3 1/5/2009 10 10.3333
item3 1/6/2009 10 10.1111
item3 1/7/2009 10 10.0000

The end of this solution must be:

item datum volume avg3day
item1 1/1/2009 10 10.0000 ->= (10)/1
item1 1/2/2009 11 10.5000 ->= (10+11)/2
item1 1/3/2009 12 11.0000 ->= (10+11+12)/3
item1 1/4/2009 11 11.3333 ->= (11+12+11)/3
item1 1/5/2009 10 11.0000 ->= (12+11+10)/3
item1 1/6/2009 10 10.3333 ->= (11+10+10)/3
item1 1/7/2009 10 10.0000 ->= (10+10+10)/3
item2 1/1/2009 10 10.0000
item2 1/2/2009 10 10.0000
item2 1/3/2009 10 10.0000
item2 1/4/2009 10 10.0000
item2 1/5/2009 10 10.0000
item2 1/5/2009 10 10.0000
item2 1/6/2009 10 10.0000
item3 1/1/2009 10 10.0000
item3 1/2/2009 10 10.0000
item3 1/3/2009 10 10.0000
item3 1/4/2009 10 10.0000
item3 1/5/2009 10 10.0000
item3 1/6/2009 10 10.0000
item3 1/7/2009 10 10.0000

Hope someone can tell me what to do and how to do this.
Its np, if i need to make a new table to store the calculated info.

Nobloz

[nobloz]

find the solution :-)

SELECT 
t1.item,t1.datum, volume, 
( SELECT SUM(t2.volume) / COUNT(t2.volume) 
FROM tradecount AS t2 
WHERE item=t1.item AND DATEDIFF(t1.datum, t2.datum) BETWEEN 0 AND 2 
) AS 'avg3day' 
FROM tradecount AS t1 
GROUP BY t1.item,t1.datum;

greetz, nobloz