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