I have a table that looks like this:

I want to run a select * query,by using a new column called cumulative_sum, such that this new column is not created to the table rather is being queried (calculated) every time and use to select rows lying between limit and offset, 1 more row after it.

Suppose I provide offset = 130 and limit = 25 (i.e. range 130 to 155) then it should return row 2 row3 and row 4 (ie. last row 1)

How to write a Mysql query for this condition?

I trying to achieve this for quite some time now please help me to understand how to do this?

The cummulative sum column is just sum of = last rows cummulative sum count of present row

CodePudding user response：

On MySQL 8 , we can simply use SUM() as an analytic function:

SELECT id, count, SUM(count) OVER (ORDER BY id) AS cumulative_sum
FROM yourTable
ORDER BY id;

On earlier versions of MySQL, we can use a correlated subquery to find the rolling sum:

SELECT id, count,
       (SELECT SUM(t2.count) FROM yourTable t2
        WHERE t2.id <= t1.id) AS cumulative_sum
FROM yourTable t1
ORDER BY id;

CodePudding user response：

Use Window function to achieve this (works with MySQL 8.0 and above):

SELECT id, count, sum(count) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cummulative_sum
FROM your_table
ORDER BY id