'MariaDB Window Function (LAG)

I'm trying to get the difference in revenue between consecutive trading months using a LAG function to look at the preceding month's figures. My query looks like this:

SELECT 
    MONTH(`Invoice_Date`) AS 'Month', 
    ROUND(SUM(`Sales_Total`),2) AS 'revenue_month',
    LAG(SUM(`Sales_Total`), 1) OVER (ORDER BY MONTH(`Invoice_Date`)) AS 'revenue_previous_month'
FROM `sales_2021`
GROUP BY MONTH(`Invoice_Date`)
ORDER BY MONTH(`Invoice_Date`);

Trying this in phpMyAdmin first shows an error for the alias 'revenue_previous_month' saying alias previously found. I tried removing the alias, but then I just get an error saying #1111 - Invalid use of group function

It does seem to be the LAG function causing the issue as removing that line altogether means the query works no problem, but I can't see anything in the line which might cause issues.

I'm pretty new to WINDOW functions and this is my first time trying LAG to get a value from the preceding row, so I'm sure I probably missed something basic, but I can't find out what!

Help please!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source