Combine multiple row data using sum() over(Partion by) in mysql

Question

SELECT orders.Stock ,lflayouts.sides, count(*) as Quantity FROM dash_relationship 
JOIN orders ON orders.UID = dash_relationship.form_id
JOIN lfitems ON lfitems.uid = orders.UID
Join lflayouts ON lflayouts.id = lfitems.layout_id
WHERE dash_relationship.machine_id='108'
GROUP BY orders.stock,lflayouts.sides;

The above query outputs as follows

STOCK   SIDES    QUANTITY
paper1    1         214
paper1    2         210
paper2    1           7
paper3    1           2

Now my question is what if I want to get total of individual stocks based on different sides. So I tried using the below query and it threw me an error saying server version for the right syntax to use near '(partition) at line 1

SELECT orders.Stock ,lflayouts.sides, count(*) as Quantity, SUM(lflayouts.sides) OVER(partition by orders.stock) as Total FROM dash_relationship 
JOIN orders ON orders.UID = dash_relationship.form_id
JOIN lfitems ON lfitems.uid = orders.UID
Join lflayouts ON lflayouts.id = lfitems.layout_id
WHERE dash_relationship.machine_id='108'
GROUP BY orders.stock,lflayouts.sides;

EXPECTED OUTPUT
STOCK   SIDES    QUANTITY TOTAL
paper1    1         214    414 or 214
paper1    2         210    414
paper2    1           7      7
paper3    1           2      2

Answer 1

MySQL 5.5.62 does not support window functions.

You can use standard join to achieve it but the query will look more complex.

SELECT    T1.Stock
         ,T1.sides
         ,Sum(T2.Quantity) as RunningTotal
FROM     (SELECT orders.Stock ,lflayouts.sides, count(*) as Quantity 
          FROM 
              dash_relationship 
              JOIN orders ON orders.UID = dash_relationship.form_id
              JOIN lfitems ON lfitems.uid = orders.UID
              JOIN lflayouts ON lflayouts.id = lfitems.layout_id
              WHERE dash_relationship.machine_id='108'
              GROUP BY orders.stock,lflayouts.sides
         ) T1
         INNER JOIN 
         (
          SELECT orders.Stock ,lflayouts.sides, count(*) as Quantity 
          FROM 
              dash_relationship 
              JOIN orders ON orders.UID = dash_relationship.form_id
              JOIN lfitems ON lfitems.uid = orders.UID
              JOIN lflayouts ON lflayouts.id = lfitems.layout_id
              WHERE dash_relationship.machine_id='108'
              GROUP BY orders.stock,lflayouts.sides
         ) T2
         ON   T1.sides >= T2.sides
         AND  T1.Stock = T2.Stock
GROUP BY  T1.Stock
         ,T1.sides
Order BY  T1.Stock
         ,T1.sides