LAG error

[Abeer60]

I am trying to run a simple SELECT command that includes LAG on goormIDE and keep getting an error message. I have a table with 2 columns: date, amount, and I am trying to retrieve a list of the amounts and next to each amount the amount from the previous row as opening_balance. Here's the code I use:
SELECT date, amount, LAG(amount, 1) OVER () opening_balance FROM transactions;
The error message I get is:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() opening_balance FROM transactions' at line 1".
Can anyone help a newbee with this?

 

[feherke]

Hi

Works for me :

test> [b]select[/b] [teal]*[/teal] [b]from[/b] transactions[teal];[/teal]
+------------+--------+
| date       | amount |
+------------+--------+
| 2019-12-01 |      1 |
| 2019-12-01 |    1.1 |
| 2019-12-01 |    1.2 |
| 2019-12-02 |      2 |
| 2019-12-03 |    3.3 |
| 2019-12-03 |   3.33 |
+------------+--------+
6 rows in set (0.00 sec)

test> [b]SELECT[/b] [maroon]date[/maroon][teal],[/teal] amount[teal],[/teal] LAG[teal]([/teal]amount[teal],[/teal] [purple]1[/purple][teal])[/teal] OVER [teal]()[/teal] opening_balance [b]FROM[/b] transactions[teal];[/teal] 
+------------+--------+-----------------+
| date       | amount | opening_balance |
+------------+--------+-----------------+
| 2019-12-01 |      1 |            NULL |
| 2019-12-01 |    1.1 |               1 |
| 2019-12-01 |    1.2 |             1.1 |
| 2019-12-02 |      2 |             1.2 |
| 2019-12-03 |    3.3 |               2 |
| 2019-12-03 |   3.33 |             3.3 |
+------------+--------+-----------------+
6 rows in set (0.00 sec)

test> [b]select[/b] version[teal]();[/teal]
+-------------------------+
| version()               |
+-------------------------+
| 8.0.18-0ubuntu0.19.10.1 |
+-------------------------+
1 row in set (0.00 sec)

Are you sure your MySQL server supports windowing functions ? Please note that they appeared only in version 8.0.


Feherke.
feherke.github.io