Description:
SELECT MONTH(date), SUM(sale),
AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date)
WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY AVG(SUM(sale)) OVER (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
gives the following weird error:
ERROR 3579 (HY000): Window name 'w' is not defined.
This is wrong, w is indeed defined.
Modifying it to:
SELECT MONTH(date), SUM(sale),
AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date)
WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY AVG(SUM(sale)) OVER w;
makes it work.
How to repeat:
CREATE TABLE sales(id INT AUTO_INCREMENT PRIMARY KEY, `date` DATE, sale INT);
SELECT MONTH(date), SUM(sale),
AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date)
WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY AVG(SUM(sale)) OVER (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
Description: SELECT MONTH(date), SUM(sale), AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date) WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY AVG(SUM(sale)) OVER (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING); gives the following weird error: ERROR 3579 (HY000): Window name 'w' is not defined. This is wrong, w is indeed defined. Modifying it to: SELECT MONTH(date), SUM(sale), AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date) WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY AVG(SUM(sale)) OVER w; makes it work. How to repeat: CREATE TABLE sales(id INT AUTO_INCREMENT PRIMARY KEY, `date` DATE, sale INT); SELECT MONTH(date), SUM(sale), AVG(SUM(sale)) OVER w AS sliding_avg FROM sales GROUP BY MONTH(date) WINDOW w AS (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY AVG(SUM(sale)) OVER (ORDER BY MONTH(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);