counting sql rows , adding value of every row to the next

I have a two columns and I want to add the value of every 开发者_如何学JAVArow to the next row. My table with two columns and 4 rows is like:

date           users
2011-01-01      1
2011-02-02      1
2011-03-02      2
2011-04-02      4

and I want the users to be shown like below , and add every row to it's previous row value:

users:

1
2
4
8

Is there a function in for MS SQL Server to do that?

thanks

---

In SQL Server 2005 and up (you didn't specify the version you have), you can do something like this using two CTE's (Common Table Expression) - a first one called UserSequence to put your data into an order and give it a sequential number (Sequence), and a second recursive CTE (Common Table Expression) to calculate the running total:

;WITH UserSequence AS
(
    SELECT 
        Date, Users, ROW_NUMBER() OVER(ORDER BY Date) as 'Sequence'
    FROM 
        dbo.YourTable
),
UserValues AS
(
  SELECT  
     u.Users AS 'UserValue', u.Date, u.Sequence
  FROM UserSequence u
  WHERE Sequence = 1

  UNION ALL 

  SELECT  
     u.Users + uv.UserValue AS 'UserValue', u.Date, u.Sequence
  FROM UserSequence u
  INNER JOIN UserValues uv ON u.Sequence = uv.Sequence + 1
)
SELECT
    Date, Sequence, UserValue AS 'Users'
FROM 
    UserValues
ORDER BY 
    Sequence

That should give you an output something like this:

Date                      Sequence    Users
2011-01-01 00:00:00.000       1         1
2011-02-02 00:00:00.000       2         2
2011-03-02 00:00:00.000       3         4
2011-04-02 00:00:00.000       4         8

---

With cte_Test(Sequence,Name,Value)
As
(
select  ROW_NUMBER() OVER(ORDER BY value) as 'Sequence' ,name,value from test
)

select t1.Name,sum(T2.Value) AS Value from cte_Test t1 
cross JOIN cte_Test t2 where t2.Sequence <= t1.Sequence 
group BY t1.Name
order by 2

---

Here's how you can do it:

set @total_users = 0;
select users, @total_users:=@total_users + users as total_users from tablename;

---

This is possible through Cross Join. if your table column is in Asc order.

select <T1.Text>,sum(T2.Value) AS 'Commulative Value' from <Your Table name>t1 
cross JOIN <Your Table name> t2 where t2.value<= t1.value
group BY t1.text
order by value

---

This is a solution in ANSI SQL:

SELECT users + lag(users) over (order by users)
FROM your_table
ORDER BY users

Works in PostgreSQL, Oracle, DB2 and Teradata (SQL Server does have windowing functions but unfortunately not the lag() function)