How to make "SUM(amount) over" window function work in MySQL 5.7?

Question

I have the following MariaDB SQL statement that I need to be converted to a MySQL statement

SELECT *,
SUM(amount) over (partition by voteid order by allocationid) AS TotalAmountAvailable
FROM allocation 

I get the following error message:

#1064 - 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 '(partition by voteid order by allocationid) AS TotalAmountAvailable FROM alloca' at line 2

My company live server has the following setup:

Database server - Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.25-0ubuntu0.16.04.2 - (Ubuntu)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

Answer 1

As I understand your question, the problem is with the window sum(), which MySQL 5.7 does not support (while it is available in MariaDB starting version 10.2).

You could rewrite it as a correlated subquery:

select
    a.*,
    (
        select sum(amount) 
        from allocation a1 
        where a1.voteid = a.voteid and a1.allocationid <= a.allocationid
    ) TotalAmountAvailable
from allocation a