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)