MySql - Sum row by row with limit and without over function

Question

I'll show you a table simplest than I have :

id	site	a	b	c
1	40	1	0	2
2	60	3	1	6
3	40	2	1	0

What I would like to do:

id	site	a	b	c	totalbyrow	total
1	40	1	0	2	3	3
3	40	2	1	6	9	12
2	60	3	1	0	4	16

If you look, I did an order by site, just an addition for totalbyrow and subquery for total. It could be like:

select
t.*,
(a + b + c) as totalbyrow,
(
 select sum(t2.a + t2.b + t2.c) from table t2 where t2.id <= t.id
) as total
from table t

but this query will not display the second table because I would like to use order by site. The query must do:

1. compute a + b +c
2. order rows by site (asc/desc, it doesn't matter)
3. sum row by row
4. add limit, example limit 15 will not show the site 60

sum(a+b+c) over (order by id)

works perfectly with latest MySql version but not with mine (5.5, yes it's code legacy 😅)

Thanks for help 🙏✌️

Answer 1

Maybe encapsulate this into an outer query which does what you need on top of data like

Select * 
from 
(
select
t.*,
(a + b + c) as totalbyrow,
(
 select sum(t2.a + t2.b + t2.c) from table t2 where t2.id <= t.id
) as total
from table t
)t
order by site asc

Answer 2

You can use user defined variables like this.

CREATE TABLE table1 (
  `id` INTEGER,
  `site` INTEGER,
  `a` INTEGER,
  `b` INTEGER,
  `c` INTEGER
);

INSERT INTO table1
  (`id`, `site`, `a`, `b`, `c`)
VALUES
  ('1', '40', '1', '0', '2'),
  ('2', '60', '3', '1', '6'),
  ('3', '40', '2', '1', '0');

SELECT
t1.*
FROM
(select
t.*,
(a + b + c) as totalbyrow,
@sum := @sum + (a + b + c) total
from table1 t,(SELECT @sum := 0) t2
ORDER BY site) t1
WHERE total < 15

id | site |  a |  b |  c | totalbyrow | total
-: | ---: | -: | -: | -: | ---------: | ----:
 1 |   40 |  1 |  0 |  2 |          3 |     3
 3 |   40 |  2 |  1 |  0 |          3 |     6

db<>fiddle here