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