November 14, 2005 at 9:15 am
Hi,
I have query as "Select Month, Users from tablename" which return the resultset as below:
Month Users
------ -------
1 10
2 20
3 10
4 5
5 15
I want cumulative column of USERS besides USERs column. Like below:
Month Users Cumulative
------ ------- ----------
1 10 10
2 20 30
3 10 40
4 5 45
5 15 60
Through cursor we can manage it. Can we get this directly through QUERY?
IF any good way then cursor then please suggest.
November 14, 2005 at 9:50 am
SET NOCOUNT ON
Declare @myTable TABLE
(
[Month] INT,
Users INT
)
INSERT @myTable
SELECT 1, 10 UNION
SELECT 2, 20 UNION
SELECT 3, 10 UNION
SELECT 4, 5 UNION
SELECT 5, 15
SELECT B.[Month],
B.Users,
SUM(CASE WHEN A.[Month] <= b.[Month] THEN A.Users ELSE 0 END) Cumulative
FROM
@myTable A
CROSS JOIN @myTable B
GROUP BY B.[Month], B.Users
ORDER BY B.[Month]
Regards,
gova
November 14, 2005 at 9:58 am
create table #newtable (
[Month] int,
users int,
Cumlative int)
declare @count int, @users int, @key int
SELECT @Count = 0, @users = 0, @key = 0
SELECT @key = min(month) FROM <tablename>
WHERE month > @key
While @key is not null
BEGIN
SELECT @users = users
FROM <tablename>
WHERE [month] = @key
SET @count = @count + @users
insert into #newtable select [month],users, @count as 'Count' from <tablename> where [month] = @key
SELECT @key = MIN(month)
FROM <tablename>
WHERE [month] > @key
END
select * from #newTable
drop table #newTable
November 14, 2005 at 10:06 am
I would assume when the orginal poster said he wouldn't want to use cursor that goes for loops as well.
Regards,
gova
November 14, 2005 at 10:09 am
how about something like this using govinn's table variable...?!?!
Declare @myTable TABLE ( [Month] INT, Users INT ) INSERT @myTable SELECT 1, 10 UNION SELECT 2, 20 UNION SELECT 3, 10 UNION SELECT 4, 5 UNION SELECT 5, 15 select a.[Month], a.Users, (select sum(Users) from @myTable b where b.[Month] <= a.[Month]) as total from @myTable a order by a.[Month]
**ASCII stupid question, get a stupid ANSI !!!**
November 14, 2005 at 10:17 am
That is a good solution. Uses correlation instead of cross join.
Regards,
gova
November 21, 2005 at 8:02 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply