Sequence sum of row in SQL for a table

Question

Consider below table.

ID          UserCount
----------- -----------
1           2
2           3
3           5

How can i add the another column (SequenceUserCount) with UserCount adding sequentialy.

Expected result:

ID          UserCount   SequenceUserCount
----------- ----------- ------------------
1           2           2
2           3           5
3           5           10

Answer 1

You can do this:

;WITH CTE
AS
(
   SELECT *,
  ROW_NUMBER() OVER(ORDER BY usercount) rownum
  FROM @table1
) 
SELECT 
  c1.id, 
  c1.usercount,
  SequenceUserCount = (SELECT SUM(c2.usercount) 
                       FROM cte c2
                       WHERE c2.rownum <= c1.rownum)
FROM CTE c1;

SQL Fiddle Demo

This will give you:

| ID | USERCOUNT | SequenceUserCount  |
--------------------------------------
|  1 |         2 |        2           |
|  2 |         3 |        5           |
|  3 |         5 |       10           |

Or: You can use JOIN instead of a correlated subquery, like so:

;WITH CTE
AS
(
   SELECT *,
  ROW_NUMBER() OVER(ORDER BY usercount) rownum
  FROM @table1
) 
SELECT 
  c1.id, 
  c1.usercount,
  SequenceUserCount = SUM(c2.usercount) 
FROM CTE c1
INNER JOIN CTE c2 ON c2.rownum <= c1.rownum
GROUP BY c1.id, c1.usercount;

Answer 2

try this

 select id,
   usercount       
   sum(usercount) over(order by usercount rows unbounded preceding) 
   as  sequenceusercount
 from Table