Calculating Running totals across rows and grouping by ID

Question

I want to compute running row totals across a table, however the totals must start over for new IDs

https://i.stack.imgur.com/8UDwZ.jpg

My code:

set @csum := 0;
select ID, name, marks, (@rt := @rt + marks) as Running_total from students order by ID;

The output returns the totals however doesn't break or start over for new IDs

Answer 1

Bro try this... It is tested on MSSQL..

select ID, name, marks, 
    marks + isnull(SUM(marks) OVER (  PARTITION BY ID   ORDER BY  ID  ROWS BETWEEN UNBOUNDED PRECEDING  AND  1  PRECEDING) ,0) as Running_total
from students 

Answer 2

You need to partition your running total by ID. A running total always needs an order of some column, by ordering on which you want to calculate the running total. Assuming running total under each ID is based on ORDER of marks,

Approach 1: It can be written in a simple query if your DBMS supports Analytical Functions

SELECT     ID
           ,name
           ,marks
           ,Running_total = SUM(marks) OVER (PARTITION BY ID ORDER BY marks ASC)
FROM       students

Approach 2: You can make use of OUTER APPLY if your database version / DBMS itself does not support Analytical Functions

SELECT     S.ID
           ,S.name
           ,S.marks
           ,Running_total = OA.runningtotalmarks 
FROM       students S
           OUTER APPLY (
                           SELECT   runningtotalmarks = SUM(SI.marks)
                           FROM     students SI
                           WHERE    SI.ID = S.ID
                           AND      SI.marks <= S.marks
                       ) OA;

Note:- The above queries have been tested MS SQL Server.