0

How to manage the cumulative sum for each user in the SQL server

 2 years ago
source link: https://www.codesd.com/item/how-to-manage-the-cumulative-sum-for-each-user-in-the-sql-server.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

How to manage the cumulative sum for each user in the SQL server

advertisements

I had a table like

ID UserID     rupees  time 

1    1       200   2014-01-05
---------------------------------
2    1     500    2014-04-06
----------------------------------
3    2      10     2014-05-05
----------------------------------
4    2      20     2014-05-06
----------------------------------

I want the output lie

ID    UserID  Rupees    time         CumulativeSum

1      1       200   2014-01-05       200
-------------------------------------------------
2      1       500   2014-04-06       700
-------------------------------------------------
3      2        10    2014-05-06        10
-------------------------------------------------
4      2        20    2014-05-06        30
---------------------------------------------------

How can i get this table as purput

Please try using CTE:

;With T as(
select
    *,
    ROW_NUMBER() over(partition by UserId order by [time]) RN
from tbl
)
select
    UserID,
    rupees,
    [time],
    (select SUM(rupees)
    from T b
    where b.UserID=a.UserID and b.RN<=a.RN) CumulativeSum
from T a

For records with column value time increasing, try the below query:

select
    UserID,
    rupees,
    [time],
    (select SUM(rupees)
    from tbl b
    where b.UserID=a.UserID and b.[time]<=a.[time]) CumulativeSum
from tbl a

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK