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:
- compute a b c
- order rows by site (asc/desc, it doesn't matter)
- sum row by row
- 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