Trying to calculate a moving average and updating a table with it

[Baukep]

Hello,

I'm learning SQL and now I'm trying to update a table (koersen) with stockdata (date, ticker, open, close, volume, MA10vol).
The column MA10vol is empty and should contain the avg volume from the last ten records with the same ticker.
What I tried sofar:

SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,10);
SELECT tickerVL, AVG(totvolume) AS MA10
FROM(
SELECT SUM(k.volume) AS totvolume
FROM koersen k WHERE k.datum BETWEEN @hdat AND @ldat
GROUP BY k.tickerVL
) AS tijdelijk

The update part comes later. I'm now trying to make a subquery to get me the total volume of the last ten days. But I get a message that the subquery returns more then 1 row (more tickers I think).

But I want a query that updates every MA10vol for every ticker. I know I'm doiing something wrong but what?

Thanks for any help!

Bauke

 

[towerbase]

I think that your use of LIMIT is wrong in the second query. At present it is selecting the first 10 records. You actually want only the 10th record so the LIMIT clause should be:

LIMIT 10,1

which means skip 10 records and select 1 record.

Andrew
Hampshire, UK

 

[Baukep]

Hello Andrew,

No, I indeed need the ten records to total the volume and get the average from that. That average (MA) is the average for the last date. For the next day the avg of the ten newest date is used.

Hope you understand my English

Bauke

 

[Baukep]

Sorry Andrew,

In my last answer I talked about the third query!

You are completely right for the second!

Thanks a lot!

Bauke

 

[Baukep]

With this query I get the values I want:

SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 9,1);
SELECT tickerVL, MAvolume
FROM
(
SELECT k.tickerVL, ROUND(AVG(k.volume),0) AS MAvolume
FROM koersen k WHERE (k.datum >= @ldat AND k.datum <= @hdat)
GROUP BY k.tickerVL
) AS tmp
GROUP BY tickerVL
ORDER BY tickerVL

But when I try to change the third select to:
UPDATE koersen
SET MA10vol = (SELECT MA10vol
FROM (SELECT k.tickerVL etc
I get errors about derived tables.

The Update WHERE should be: WHERE datum = @hdat

But where do I put it?

Bauke

 

[towerbase]

What is the complete SQL statement that is failing?

What are the actual error messages that you are getting?

Andrew
Hampshire, UK

 

[Baukep]

This one works:
SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 9,1);
SELECT tickerVL, MAvolume
FROM
(
SELECT k.tickerVL, ROUND(AVG(k.volume),0) AS MAvolume
FROM koersen k WHERE (k.datum >= @ldat AND k.datum <= @hdat)
GROUP BY k.tickerVL
) AS tmp
GROUP BY tickerVL
ORDER BY tickerVL

Changed to:
SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 9,1);
UPDATE koersen
SET MA10vol = (SELECT tickerVL, MAvolume
FROM
(
SELECT k.tickerVL, ROUND(AVG(k.volume),0) AS MAvolume
FROM koersen k WHERE (k.datum >= @ldat AND k.datum <= @hdat)
GROUP BY k.tickerVL
) AS tmp
GROUP BY tickerVL
ORDER BY tickerVL) as tmp1
WHERE datum = @hdat

It gives a syntax error in the last 2 rows at 'as tmp1 WHERE datum = @hdat.
I tried some other things but get errors too.

 

[towerbase]

Your statement

SET MA10vol = (SELECT tickerVL, MAvolume ...

seems to be setting MA10vol to two values (tickerVL and MAvolume) which will cause an error.

I don't think as tmp1 is correct in the second last line. It should be removed.

Andrew
Hampshire, UK

 

[Baukep]

I changed it and now the third query return more then one row (more tickers I think). How do I accomplish that it only gives the ticker that is cuurently updated.

Excuse my English. Hope you understand what I mean.

Bauke

 

[towerbase]

What is the third query?

Andrew
Hampshire, UK

 

[Baukep]

The UPDATE query

Bauke

 

[towerbase]

The UPDATE should not return anything except a message to say how many rows were affected.

Andrew
Hampshire, UK