I have a two columns and I want to add the value of every row 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)
